[sqlite] is it possible to query for database file name ?
in php I declared database object: $pdo = new PDO('sqlite:mybase.DB3'); i know how to get information about engine used in this connection, which is: $pdo->getAttribute(PDO::ATTR_DRIVER_NAME); ---> string 'sqlite' But I do not know how to get back the actual database file name back from this object. Is it possible to be done from query to the database itself (maybe some kind of PRAGMA) or from query to the object. -- View this message in context: http://old.nabble.com/is-it-possible-to-query-for-database-file-name---tp28458950p28458950.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Expression tree is too large
Hi guys, I've got a DELETE statement with a lot of OR: DELETE FROM myTable WHERE id = ? OR id = ?..OR id=? and SQLite throws this error: Expression tree is too large. Do you know a way to avoid such problem or I just have to split the large statement into shorter ones? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expression tree is too large
> - Ursprüngliche Nachricht - > Von: Andrea Galeazzi > Gesendet: 05.05.10 12:14 Uhr > An: General Discussion of SQLite Database > Betreff: [sqlite] Expression tree is too large > Hi guys, I've got a DELETE statement with a lot of OR: DELETE FROM myTable WHERE id = ? OR id = ?..OR id=? -> ... WHERE id IN (?, ?, ...) Frank ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Expression tree is too large
Thanks, it works! Frank Baumgart ha scritto: >> - Ursprüngliche Nachricht - >> Von: Andrea Galeazzi >> Gesendet: 05.05.10 12:14 Uhr >> An: General Discussion of SQLite Database >> Betreff: [sqlite] Expression tree is too large >> >> > Hi guys, > I've got a DELETE statement with a lot of OR: > DELETE FROM myTable WHERE id = ? OR id = ?..OR id=? > > > > -> ... WHERE id IN (?, ?, ...) > > Frank > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > __ Informazioni da ESET NOD32 Antivirus, versione del database delle > firme digitali 5087 (20100505) __ > > Il messaggio è stato controllato da ESET NOD32 Antivirus. > > www.nod32.it > > > > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is it possible to query for database file name ?
On 5 May 2010 11:08, yogibabu wrote: > > in php I declared database object: > $pdo = new PDO('sqlite:mybase.DB3'); > > i know how to get information about engine used in this connection, which > is: > $pdo->getAttribute(PDO::ATTR_DRIVER_NAME); ---> string 'sqlite' > > But I do not know how to get back the actual database file name back from > this object. Is it possible to be done from query to the database itself > (maybe some kind of PRAGMA) or from query to the object. PRAGMA database_list; see http://www.sqlite.org/pragma.html#pragma_database_list Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT questions
On Tue, 4 May 2010 14:22:25 +0100, "Mark Coles" wrote: >Hi there, > > trying to INSERT a new row into a table and got a few > errors which I think I've sorted, I was getting a > couple of errors that date columns for FirstCreated > and LastModified (datetime Type) may not be NULL, > so I included them in my insert line as follows > > INSERT INTO Aircraft (FirstCreated,LastModified,ModeS) > values ('2010-05-04 09:21:31','2010-05-04 09:21:31','C4'); Datetime is not a type in SQLite, you are storing date/time stamps as TEXT here. > and it seems to have worked as that row is now > in my (test)database, what I was wondering about > were the boolean fields, they are all filled in > with zeroes, so this is a good sign, yes? Boolean is not a type in SQLite. You are encoding them as integer, deliberately or by accident. We have no way to know whether zero is a proper value. > Also, what about the relationships between this table > and others in the database, should everything be OK > as I want to share this but not screw up other peoples databases We cannot answer that question without knowing 1- the schema of your database 2- the schema of other peoples database 3- a type-and-data dump of your database 4- a type-and-data dump of other peoples databases You can generate 3 and 4 yourself with something like SELECT typeof(col1 AS t_col1), col1, typeof(col1 AS t_col2), col2, ... FROM atable. I think it is your job to do that comparison, not ours. If you find anything irregular you can't understand after consulting http://www.sqlite.org/datatype3.html and http://www.sqlite.org/lang_datefunc.html you can ask that specific question here. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A newb performance question
Given the example below where 'first_table' could be huge, are sqlite or other relational databases clever enough to do the WHERE filter before doing the joins. SELECT * FROM first_table INNER JOIN second_table ON second_table_id = second_table.id INNER JOIN third_table ON third_table_id = third_table.id WHERE first_table.target_field=x or would it be better to do SELECT * FROM (SELECT * FROM first_table WHERE target_field=x) INNER JOIN second_table ON second_table_id = second_table.id INNER JOIN third_table ON third_table_id = third_table.id ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing SQLite from Lua?
Hello >From what I've been reading this morning, it appears that Lua offers two ways to access an SQLite database: - through the database-neutral Lua API LuaSQL - by calling the SQLite-specific luasqlite I have no preference, and would like to find the Windows binaries that I could just copy to the directory where the Lua interpreter is located and access SQLite through eg. "require("luasql.sqlite")". The only SQLite file in the "Lua for Windows" package is this: "C:\Program Files\Lua\5.1\clibs\luasql\sqlite3.dll" I'd like to know how to use this from a Lua script. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is it possible to query for database file name ?
On Wed, 5 May 2010 03:08:42 -0700 (PDT), yogibabu wrote: > >in php I declared database object: >$pdo = new PDO('sqlite:mybase.DB3'); > >i know how to get information about engine used in this connection, which >is: >$pdo->getAttribute(PDO::ATTR_DRIVER_NAME); ---> string 'sqlite' > >But I do not know how to get back the actual database file name back from >this object. Is it possible to be done from query to the database itself >(maybe some kind of PRAGMA) or from query to the object. You already know the filename, you passed it to the constructor. If you want to maintain more state information, just subclass the PDO classes which do have the extra attributes you desire. The constructor can validate the database, and create a new one when needed. In example below I a text file with the schema to PDO in order to create the database. sqlitedbpath = SQLITEDBPATH; $this->sqlitedbname = $dbname; /* Make connection to database */ try { $catchmsg = 'Failed to connect to database '.$this->sqlitedbname.', '; parent::__construct('sqlite:'.$this->dbfile()); $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = 'SELECT COUNT(name) AS tblcount FROM sqlite_master WHERE type == \'table\''; $res = $this->query($sql); if (!$res){ throw new Exception('Error on query sqlite_master table count'); } $row = $res->fetch(); if (!$row){ throw new Exception('Error on fetch sqlite_master table count'); } $tblcount = $row['tblcount']; if ($tblcount < 3){ $fschema = 'inc/mk'.$this->sqlitedbname.'.sql'; if (file_exists($fschema)){ $catchmsg = 'Failed to initialize database '.$this->sqlitedbname.', '; /** * remove -- . \n comments -> \n * replace 3 newlines by a single one * replace 2 newlines by a single one */ $schema = preg_replace(array( '/\s*--[^\n]*\n/', '/\n\n\n/', '/\n\n/'),array( "\n", "\n", "\n"),file_get_contents($fschema)); $this->exec($schema); unset($schema); } else { throw new Exception('No schemafile '.$fschema.' present, can\'t create database.'); } } return TRUE; } catch (PDOException $e) { error_log($catchmsg . $e->getMessage()); error_log(print_r($this->errorInfo(),TRUE)); return(FALSE); } } // end __construct() /** * For debugging: tell us when we're done. * Comment out the whole function to suppress. */ function __destruct(){ error_log(__FUNCTION__.' '.$this->sqlitedbname); } /** * Return the full disk:/path/filename.ext of the sqlite database file. * */ function dbfile(){ return $this->sqlitedbpath.$this->sqlitedbname.'.db3'; } etcetera. }; // end class sqlitedb ?> -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A newb performance question
Astley Le Jasper wrote: > Given the example below where 'first_table' could be huge, are sqlite > or other relational databases clever enough to do the WHERE filter > before doing the joins. I would expect SQLite to do it this way, yes. Especially if there's an index on first_table.target_field. You can confirm with EXPLAIN QUERY PLAN -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementing a CREATE_FUNCTION function
Dan Bishop wrote: > It's convenient to be able to define new functions in C. But sometimes, > it would be *more* convenient to be able to define new functions in > SQL. This could be done by registering a CREATE_FUNCTION() function; > then you could write something like: > > SELECT CREATE_FUNCTION('LEFT', 2, 'SUBSTR(?1, 1, ?2)'); > > My first idea for implementing CREATE_FUNCTION is: > > 1. Create (if not exists) a table with columns for the function name, > number of arguments, and SQL expression. > 2. Add the new function to the table. > 3. Call sqlite3_create_function to register the new function. > > C doesn't have the ability to create functions at runtime, so the xFunc > parameter would refer to a common global function, which would: > > 1. Look up the SQL expression corresponding to the SQL function name. > 2. Evaluate the expression. > > But how do I get the SQL function name from within the xFunc function? > Can I get it from the sqlite3_context object, or do I have to use > sqlite3_user_data()? You have to use sqlite3_user_data. In fact, it's not clear why you would want to store anything in the table. Just allocate some structure describing your new "function" (containing the same information that you planned to store in that table), and pass its address to sqlite3_create_function, to be picked up with sqlite3_user_data. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] is it possible to query for database file name ?
On 5 May 2010, at 11:08am, yogibabu wrote: > in php I declared database object: > $pdo = new PDO('sqlite:mybase.DB3'); > > i know how to get information about engine used in this connection, which > is: > $pdo->getAttribute(PDO::ATTR_DRIVER_NAME); ---> string 'sqlite' > > But I do not know how to get back the actual database file name back from > this object. Is it possible to be done from query to the database itself > (maybe some kind of PRAGMA) or from query to the object. It is indeed. Check out 'PRAGMA database_list' in this page http://www.sqlite.org/pragma.html However, there's one caveat: you may or may not get back the name you used when opening the database in the first place. Some filenames are aliases or pointers to other files. And sometimes a file path gets expanded or otherwise interpreted when used (e.g. the use of '~' for the home folder. So if your code is sensitive to either of these you may have to figure out how your particular installation of PHP is doing things. >From a brief look at the PDO documentation you're right: the PHP PDO system >has no method of returning the name of the database. This may be because many >database systems keep the information for one database in many different >files. For instance, MySQL makes a folder with five files in before you've >even created your first TABLE. And some systems don't keep separate databases >in a trackable way anyway: they really just keep all their databases in an >internal 'black box'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from Lua?
On Wed, 05 May 2010 13:32:24 +0200, Gilles Ganault wrote: >I'd like to know how to use this from a Lua script. For Lua newbies like me who'd like to access an SQLite database through the LuaSql interface, here's how to do it: 1. In the directory where the Lua interpreter is located, create a sub-directory \luasql 2. In this sub-directory, from http://luaforge.net/frs/?group_id=12, download and unzip luasql-2.1.1-sqlite3-win32-lua51.zip, which contains sqlite3.dll (a Lua-specific sqlite3.dll, not the one from www.sqlite.org) 3. In the main directory, create a source file eg. test.lua: -- require "luasql.sqlite3" env = luasql.sqlite3() conn = env:connect("test.sqlite") assert(conn:execute("create table if not exists tbl1(one varchar(10), two smallint)")) assert(conn:execute("insert into tbl1 values('hello!',10)")) assert(conn:execute("insert into tbl1 values('goodbye',20)")) cursor = assert(conn:execute("select * from tbl1")) row = {} while cursor:fetch(row) do print(table.concat(row, '|')) end cursor:close() conn:close() env:close() -- 4. Launch this script: lua5.1.exe test.lua HTH, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite in low memory devices
Well I have a port to latest version (3.6.23.1) of sqlite to palmos, and in some devices with this S.O. had only 2MB of Dynamic Heap. On this environments sqlite doesn't works pretty well, then to work I had to set the cache size (PRAGMA cache_size) for all attached database, calculating the possible value based on the amouth of free memory and the page size. Hanging this on mind, on vacuum I had to do the same thing, but on vacuum the sqlite make attach of a memory database and that need use the same size of cache used by main database, then, I modified the sqlite code to do this, and now vacuum will work. I'm sending attached the path for this. -- Atenciosamente/Regards, Israel Lins Albuquerque Desenvolvimento/Development Polibrás Brasil Software Ltda. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?
On Mon, May 03, 2010 at 03:01:26PM +0400, Alexey Pechnikov wrote: > See http://wiki.tcl.tk/15722 Add SQLite into it - about few minuts of time. > > 2010/5/3 Gilles Ganault : > > I don't know if it'd be easier to combine existing HTTP server + > > SQLite ditto http://code.google.com/p/mongoose/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [server] HTTP + SQLite bundled as single EXE?
On Mon, 3 May 2010 08:57:04 -0400, Reid Thompson wrote: >http://code.google.com/p/mongoose/ Right, I got Lua and SQLite working as a CGI call. The alternative is to use the Lua-based Xavante web server and include SQLite; I'll try to get this working tomorrow. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
I interpret the silence on the lis that anyone agrees that SQLite has a bug because there seems to be no way to get VIEWS returning the column type if the column is calculated or a function. This also breaks compatibility as mentioned in http://www.sqlite.org/datatype3.html ("SQL statement that work on statically typed databases should work the same way in SQLite."). => Time for a ticket? -S. 2010/5/3 Stefan Keller : > Unfortunately the application which reads from this view needs that > all columns are typed - even if the values types deviate from it - and > I think this is a logical assumption. So, I fear I do have only one > chance and SQLite doesn't let me do it: > > CREATE VIEW myview AS > SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; > > To me it seems like an inconsistency (or bug) when TABLES allow > declaration of types and VIEWS only eventually. > > -S. > > 2010/5/3 Simon Slavin : >> >> On 3 May 2010, at 6:14pm, Stefan Keller wrote: >> >>> But in SQLite if a view column comes from a function result or some >>> computation, then the column type is NULL...!? It's not taking the >>> result-type as mentioned in the manual >>> (http://www.sqlite.org/lang_select.html) - even when I try to do a >>> CAST in the projection clause, like this: >>> >>> SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; >>> >>> I mean that a VIEW should behave like a (read only) TABLE in any case. >>> => Is there a way to give such columns a type anyway? >> >> You get two chances to CAST, one when you define the VIEW, and another when >> you SELECT from the VIEW. If one of them doesn't enforce the type of >> evaluation you want, the other probably will. As you say, VIEW columns >> don't have types at all. >> >> Simon. >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
> I interpret the silence on the lis that anyone agrees that SQLite has a bug Generally silence on this list means that everybody disagrees with you and/or doesn't see enough arguments in your email to even start any discussion. When everybody agrees that SQLite has a bug you get a lot of responses almost immediately. > because there seems to be no way to get VIEWS returning the column type if the > column is calculated or a function. I've asked you a question and you didn't seem to answer it. Why do you want declared data type (which basically doesn't have any meaning in SQLite at all) in a view in a first place? Why having value type is not enough? Why don't you describe your situation and what you are trying to achieve in more details? > This also breaks compatibility as > mentioned in > http://www.sqlite.org/datatype3.html ("SQL statement that work on > statically typed > databases should work the same way in SQLite."). > => Time for a ticket? No, it doesn't break compatibility. Queries work the same way as in other databases and return to you the same results. AFAIK, queries don't have to always return to you information about data type of underlying table column or function (which btw can return different data type for each row in SQLite). Datatype system is completely different in SQLite and nobody claimed it should be compatible... Pavel On Wed, May 5, 2010 at 5:32 PM, Stefan Keller wrote: > I interpret the silence on the lis that anyone agrees that SQLite has a bug > because there seems to be no way to get VIEWS returning the column type if the > column is calculated or a function. This also breaks compatibility as > mentioned in > http://www.sqlite.org/datatype3.html ("SQL statement that work on > statically typed > databases should work the same way in SQLite."). > => Time for a ticket? > > -S. > > 2010/5/3 Stefan Keller : >> Unfortunately the application which reads from this view needs that >> all columns are typed - even if the values types deviate from it - and >> I think this is a logical assumption. So, I fear I do have only one >> chance and SQLite doesn't let me do it: >> >> CREATE VIEW myview AS >> SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; >> >> To me it seems like an inconsistency (or bug) when TABLES allow >> declaration of types and VIEWS only eventually. >> >> -S. >> >> 2010/5/3 Simon Slavin : >>> >>> On 3 May 2010, at 6:14pm, Stefan Keller wrote: >>> But in SQLite if a view column comes from a function result or some computation, then the column type is NULL...!? It's not taking the result-type as mentioned in the manual (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in the projection clause, like this: SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; I mean that a VIEW should behave like a (read only) TABLE in any case. => Is there a way to give such columns a type anyway? >>> >>> You get two chances to CAST, one when you define the VIEW, and another when >>> you SELECT from the VIEW. If one of them doesn't enforce the type of >>> evaluation you want, the other probably will. As you say, VIEW columns >>> don't have types at all. >>> >>> Simon. >>> ___ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
On 04/05/2010, at 3:14 AM, Stefan Keller wrote: > But in SQLite if a view column comes from a function result or some > computation, then the column type is NULL...!? It's not taking the > result-type as mentioned in the manual > (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in > the projection clause, like this: > > SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; > > I mean that a VIEW should behave like a (read only) TABLE in any case. > => Is there a way to give such columns a type anyway? I've had the same issue. In the end I had to parse my view functions in my own code and look for functions that give a particular type of result. So, for instance, round() gives an integer, round(..., 2) gives a real, concat() gives text. I also look for a cast(... as type) to use that declared type. It's fiddly and I would have hoped SQLite would have at least declared the type if a cast was present, but it seems not. I actually raised this issue on this list back in September 2009: On 22/09/2009, at 10:08 AM, BareFeet wrote: >> 4. Improve the declared_type result of a column to show a declared type for >> an expression in a view. The declared_type C call and the pragma >> table_info() SQL call currently return the type of a column in a view if the >> column is simply referring to a column in a table. But if the column in the >> view is an expression, it returns null. I would like to see it return the >> declared type of the outer function of the expression, since most functions >> have a known result type. For instance, if a column in a view is defined as >> cast(mycomplexfunction() as integer), then the declared type is known to be >> an integer. If the function is abs(), the type is real. If the function is >> length(), the type is integer. Obviously a few functions such as coalesce() >> have varied types so they would still have to show a declared_type of null, >> but the cast() function gives the SQL author the chance to explicitly >> declare the type of such a column. On 06/05/2010, at 7:59 AM, Pavel Ivanov wrote: >> I interpret the silence on the lis that anyone agrees that SQLite has a bug > > Generally silence on this list means that everybody disagrees with you and/or > doesn't see enough arguments in your email to even start any > discussion. When everybody agrees that SQLite has a bug you get a lot of > responses almost immediately. In my case, my silence was "oh good, someone else is mentioning this issue. I won't waste bandwidth by saying "me too" but will watch closely for any replies". >> Why do you want declared data type (which basically doesn't have any meaning >> in SQLite at all) in a view in a first place? Why having value type is not >> enough? I want declared types of a view column for the same reasons I want declared types of a table column. For instance, I can format the output correctly, such as tight aligned for integers, two decimal places for reals, left aligned and wider for text, special handling for a blob. Only having value type means I have to search through the results of a query to find the first non-null result value and examine it's type and hope that it's the same as the column's (un)declared type. That's inefficient. Obviously, when I am displaying individual cells of data I can look at the value type, but the declared type of the column means I can pre-format the column before displaying the data. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT question (computing day of week the usingstrftime() function)
Please cancel my subscription to this list!! My Mail client is overwhelmed. Roger Andersson wrote: >> I need to find out how many specific weekdays (e.g., how many >> Sundays) I have in any given range of dates. >> My problem: How to use the COUNT function in combination with >> the strftime() function. >> >> > Maybe something like > > sqlite3 test.db > SQLite version 3.6.23 > sqlite> CREATE TABLE test (date TEXT, money INTEGER); > sqlite> INSERT INTO test VALUES('2007-07-20', 1000); > sqlite> INSERT INTO test VALUES('2007-07-21', 2100); > sqlite> INSERT INTO test VALUES('2007-07-22', 2200); > sqlite> INSERT INTO test VALUES('2007-07-27', 7000); > sqlite> INSERT INTO test VALUES('2007-07-28', 2800); > sqlite> INSERT INTO test VALUES('2007-07-29', 2900); > sqlite> INSERT INTO test VALUES('2007-07-22', 9200); > sqlite> SELECT strftime('%w', date) weekday, count(*) cnt FROM test GROUP BY > weekday; > weekday|cnt > 0|3 > 5|2 > 6|2 > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read-only tables in in-memory database?
There seems to be lots of information in the mailing list regarding read-only databases and locking when it comes to databases on disk. Both locking and read-only mode seems to be functionality requiring a file on disk. Is it even possible to have locking or read-only mode with in-memory databases? I am building a cache that will be publicly available. This is done using an SQLite :memory: database for storage. I want to fill a table with data and then make it read-only to prevent any SQL injection attempts. Regular SQL injection mitigation techniques such as parameterization is not possible in this application. In a stand-alone database engine this could have been handled with access control but using SQLite I see no way to prevent SQL Injection in my specific application. A read-only attribute on a table would have solved my problem. Locking could possibly be used to solve this problem but I am unsure. Any ideas? Best regards, Daniel Haglund ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A newb performance question
Hi Igor, I'm getting "0","0","TABLE first_table WITH INDEX first_table_target_field_id" "1","1","TABLE second_table USING PRIMARY KEY" "2","2","TABLE third_table USING PRIMARY KEY" ... so I presume it is then. (Is there anywhere that explains how to interpret this?) Thanks ALJ On May 5, 1:45 pm, "Igor Tandetnik" wrote: > Astley Le Jasper wrote: > > Given the example below where 'first_table' could be huge, are sqlite > > or other relational databases clever enough to do the WHERE filter > > before doing the joins. > > I would expect SQLite to do it this way, yes. Especially if there's an index > on first_table.target_field. You can confirm with EXPLAIN QUERY PLAN > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-us...@sqlite.orghttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] I don't understand locking
Dear Users I've spent hours reading various web-based documents, examined lots of code snippets, written some code of my own, but I still patently do not understand SQLite locks. My misunderstanding is probably best illustrated with a concrete example written in C. #include #include #include int main() { sqlite3 *db; int error; sqlite3_stmt *res; int count; char *sqlite_query; error = sqlite3_open_v2("simple.db3", &db, SQLITE_OPEN_READWRITE, 0); if(error) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sqlite3_busy_timeout(db, 1); sqlite3_exec(db, "BEGIN", NULL, NULL, NULL); error = sqlite3_prepare_v2(db, "select count from table1", 100, &res, NULL); if (error != SQLITE_OK) { printf("SQLITE_OK is not OK - %d\n", error); exit(1); } while (sqlite3_step(res) == SQLITE_ROW) { count = sqlite3_column_int(res, 0); printf("%u", count); } sqlite3_finalize(res); count++; sqlite_query = sqlite3_mprintf("update table1 set count = %d", count); error = sqlite3_exec(db, sqlite_query, NULL, NULL, NULL); if(error != SQLITE_OK) { puts(" - error updating count"); } else { puts(" - updated OK"); } sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); sqlite3_free(sqlite_query); sqlite3_close(db); return 0; } When I run this serially, I get: $ ./demo 214 - updated OK $ ./demo 215 - updated OK $ ./demo 216 - updated OK When I run it in parallel, using this script: #!/bin/sh count=0 limit=4 while [ ${count} -lt ${limit} ] do count=`expr $count + 1` ./demo 2>&1 & done I get: $ ./multi.sh 219 - updated OK 219 - error updating count 220 - updated OK 220 - error updating count I thought that this line... sqlite3_busy_timeout(db, 1); ...would give me a 10 second window where SQLite would gracefully, invisibly take care of locking for me, as per this documentation: "This routine sets a busy handler that sleeps for a specified amount of time when a table is locked. The handler will sleep multiple times until at least "ms" milliseconds of sleeping have accumulated. After at least "ms" milliseconds of sleeping, the handler returns 0 which causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED." But things go wrong long before 10 seconds have elapsed, and I get the error messages shown. Now, clearly, SQLite is capable of handling locks, and the fault lies with me. Maybe I've simply missed a nuance somewhere, or maybe my understanding is fundamentally wrong. I just don't know. I would turn cartwheels if somebody could show me what I'm doing wrong in my code, and spoon-feed me the canonical way of doing it the right way. I am keen to use SQLite, but this locking impass is currently a sticking point for me. Many thanks Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Misspelling in SQLite
Hi, I made a patch for Mozilla Firefox and I got told that Mozilla doesn't want to fix upstream bugs and that I need to send the patch to you. The attached patch is a patch against Mozilla Firefox code, but I guess it won't be that hard to convert it to a "normal" patch. Feel free to reply if you have any questions. Sincerely, Michael Kohler _ http://redirect.gimas.net/?n=M1004xNoSpam2 Angst vor Spam? Hotmail schützt Sie mit modernster Technologie!___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Misspelling in SQLite 2
Hi again, I got another patch for SQLite in which I changed "non-existent" to "nonexistent". Maybe you're interested in this too. Sincerely, Michael Kohler diff -r fa1e1974b034 security/nss/lib/sqlite/sqlite3.c --- a/security/nss/lib/sqlite/sqlite3.cWed May 05 21:57:11 2010 +0200 +++ b/security/nss/lib/sqlite/sqlite3.cThu May 06 02:23:19 2010 +0200 @@ -98109,17 +98109,17 @@ SQLITE_PRIVATE void sqlite3ConnectionClo **MERGE_COUNT segments ** 16 25 **8 12 **4 10 **26 ** ** This appears to have only a moderate impact on queries for very ** frequent terms (which are somewhat dominated by segment merge -** costs), and infrequent and non-existent terms still seem to be fast +** costs), and infrequent and nonexistent terms still seem to be fast ** even with many segments. ** ** TODO(shess) That said, it would be nice to have a better query-side ** argument for MERGE_COUNT of 16. Also, it is possible/likely that ** optimizations to things like doclist merging will swing the sweet ** spot around. ** ** _ http://redirect.gimas.net/?n=M1004xjajah2 Über Messenger günstiger telefonieren? Sagen Sie "Ja" zu JAJAH! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Read-only tables in in-memory database?
daniel.hagl...@trafikverket.se wrote: > There seems to be lots of information in the mailing list regarding read-only > databases and locking when it comes to databases on > disk. Both locking and read-only mode seems to be functionality requiring a > file on disk. Is it even possible to have locking or > read-only mode with in-memory databases? An in-memory database can never be accessed by more than one connection, so locking is moot. A read-only in-memory database makes no sense: the database is empty when created, so if you can't write to it, it will remain empty. What good is a database with no data in it? > I am building a cache that will be publicly available. This is done using an > SQLite :memory: database for storage. I want to > fill a table with data and then make it read-only to prevent any SQL > injection attempts. Regular SQL injection mitigation > techniques such as parameterization is not possible in this application. In a > stand-alone database engine this could have been > handled with access control but using SQLite I see no way to prevent SQL > Injection in my specific application. You can do access control via sqlite3_set_authorizer: http://sqlite.org/c3ref/set_authorizer.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I don't understand locking
On May 5, 2010, at 8:32 PM, myomancer wrote: > Dear Users > > I've spent hours reading various web-based documents, examined lots of > code snippets, written some code of my own, but I still patently do > not understand SQLite locks. 4th paragraph of this page (The presence of a busy...) describes the condition your program is hitting: http://www.sqlite.org/c3ref/busy_handler.html > > My misunderstanding is probably best illustrated with a concrete > example written in C. > > #include > #include > #include > > int main() > { > sqlite3 *db; > int error; >sqlite3_stmt *res; > int count; > char *sqlite_query; > > error = sqlite3_open_v2("simple.db3", &db, SQLITE_OPEN_READWRITE, 0); > if(error) > { > fprintf(stderr, "Can't open database: %s\n", > sqlite3_errmsg(db)); > sqlite3_close(db); > exit(1); > } > > sqlite3_busy_timeout(db, 1); > > sqlite3_exec(db, "BEGIN", NULL, NULL, NULL); > error = sqlite3_prepare_v2(db, "select count from table1", 100, &res, > NULL); > if (error != SQLITE_OK) > { > printf("SQLITE_OK is not OK - %d\n", error); > exit(1); > } > > while (sqlite3_step(res) == SQLITE_ROW) > { > count = sqlite3_column_int(res, 0); > printf("%u", count); > } > sqlite3_finalize(res); > > count++; > sqlite_query = sqlite3_mprintf("update table1 set count = %d", > count); > error = sqlite3_exec(db, sqlite_query, NULL, NULL, NULL); > if(error != SQLITE_OK) > { > puts(" - error updating count"); > } > else > { > puts(" - updated OK"); > } > > sqlite3_exec(db, "COMMIT", NULL, NULL, NULL); > sqlite3_free(sqlite_query); > sqlite3_close(db); > > return 0; > } > > When I run this serially, I get: > $ ./demo > 214 - updated OK > $ ./demo > 215 - updated OK > $ ./demo > 216 - updated OK > > When I run it in parallel, using this script: > > #!/bin/sh > > count=0 > limit=4 > while [ ${count} -lt ${limit} ] > do > count=`expr $count + 1` > ./demo 2>&1 & > done > > I get: > > $ ./multi.sh > 219 - updated OK > 219 - error updating count > 220 - updated OK > 220 - error updating count > > I thought that this line... > > sqlite3_busy_timeout(db, > 1); > > ...would give me a 10 second window where SQLite would gracefully, > invisibly take care of locking for me, as per this documentation: > > "This routine sets a busy handler that sleeps for a specified amount > of time when a table is locked. The handler will sleep multiple times > until at least "ms" milliseconds of sleeping have accumulated. After > at least "ms" milliseconds of sleeping, the handler returns 0 which > causes sqlite3_step() to return SQLITE_BUSY or SQLITE_IOERR_BLOCKED." > > But things go wrong long before 10 seconds have elapsed, and I get the > error messages shown. > > Now, clearly, SQLite is capable of handling locks, and the fault lies > with me. Maybe I've simply missed a nuance somewhere, or maybe my > understanding is fundamentally wrong. I just don't know. I would turn > cartwheels if somebody could show me what I'm doing wrong in my code, > and spoon-feed me the canonical way of doing it the right way. I am > keen to use SQLite, but this locking impass is currently a sticking > point for me. > > Many thanks > Peter > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
BareFeetWare wrote: > On 04/05/2010, at 3:14 AM, Stefan Keller wrote: > > >> But in SQLite if a view column comes from a function result or some >> computation, then the column type is NULL...!? It's not taking the >> result-type as mentioned in the manual >> (http://www.sqlite.org/lang_select.html) - even when I try to do a CAST in >> the projection clause, like this: >> >> SELECT id, name, CAST(salary + 10 AS INTEGER) "salaryplus10" FROM mytable; >> >> I mean that a VIEW should behave like a (read only) TABLE in any case. >> => Is there a way to give such columns a type anyway? >> > > I've had the same issue. In the end I had to parse my view functions in my > own code and look for functions that give a particular type of result. So, > for instance, round() gives an integer, round(..., 2) gives a real, concat() > gives text. I also look for a cast(... as type) to use that declared type. > It's fiddly and I would have hoped SQLite would have at least declared the > type if a cast was present, but it seems not. > > A CAST expression could be assigned a declared type, but it would be difficult to implement it for expressions in general. Not only would you have to declare a type for every function, but you'd have to do it for operators as well, and what type should A * B be if A is "SMALLINT" and B is "UINT"? Should relational operators have a declared type of BOOLEAN? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?
On 06/05/2010, at 2:51 PM, Dan Bishop wrote: > BareFeetWare wrote: >> >> I've had the same issue. In the end I had to parse my view functions in my >> own code and look for functions that give a particular type of result. So, >> for instance, round() gives an integer, round(..., 2) gives a real, concat() >> gives text. I also look for a cast(... as type) to use that declared type. >> It's fiddly and I would have hoped SQLite would have at least declared the >> type if a cast was present, but it seems not. >> > A CAST expression could be assigned a declared type Yes, that should be fairly easy to do in SQLite, simpler than my adding it in my own code (which then requires my own parser - not a trivial exercise). > but it would be difficult to implement it for expressions in general. Not > only would you have to declare a type for every function Yes, probably half the functions don't have a consistently returned type (eg length, concat). The ones that don't (eg coalesce) should just be left to return a null (unknown) type. The schema developer can simply wrap those expressions in a cast if they want a predetermined output type. > but you'd have to do it for operators as well, and what type should A * B be > if A is "SMALLINT" and B is "UINT"? I hadn't thought of operators. As far as I know, mathematical operators (* / + -) only work with integers and reals, Perhaps the result type could be set to "numeric" or some other superclass. Or, to get real fancy, if the two operands are declared integers, then the result is an integer; if one is real then the result is real. AFAIK SMALLINT and UINT are both treated by SQLite as integer, so the result would be an integer. > Should relational operators have a declared type of BOOLEAN? Good idea. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users