Re: [sqlite] Beginners bad luck
Aharon (Rony) Shapira wrote:
snip
$db = new SQLiteDatabase($file) or die(Could not open database);
I got the following:
Fatal error: Uncaught exception 'SQLiteException' with message
'SQLiteDatabase::__construct() [a
href='function.SQLiteDatabase---construct'function.SQLiteDatabase---construct/a]:
file is encrypted or is not a database' in
/home/zbfckla/public_html/Ocx.php:17 Stack trace: #0
/home/zbfckla/public_html/Ocx.php(17):
SQLiteDatabase-__construct('library.db') #1 {main} thrown in
/home/zbfckla/public_html/Ocx.php on line 17
Could this be Apache permissions?
Is the directory/file world readable and writable? Remember APACHE
normaly runs as user NOBODY so permissions usually need to be chmod to
666 (or 777 for DIRs) for files (or directories) that need to be written to
Eddy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Temporary tables and locking
Temporary tables are limited to an individual connection and are deleted when the connection closes. You should not be accessing the same connection by two different threads, if you do then you must not do so simultaneously but to use a MUTEX lock. With that in mind any locking on the temporary table is moot. Eddy Brandon, Nicholas wrote: Hi I don't think I fully understanding the locking strategy with temporary tables. The documentation at http://www.sqlite.org/cvstrac/wiki?p=MultiThreading suggests that Temporary tables are not locked like the normal database. If you can read and write to temporary tables and their is no locking mechanism, then I assume the scope of a temporary table is restricted. Is the scope of temporary tables within a thread or a process? Regards Nick This email and any attachments are confidential to the intended recipient and may also be privileged. If you are not the intended recipient please delete it from your system and notify the sender. You should not copy it or use it for any purpose nor disclose or distribute its contents to any other person.
Re: [sqlite] Network-based DB performance for Mozilla
To add my 2p worth to all this I am not fully aware of the Firefox internals, buts as far as my understanding goes only one process can use any profile at any time. If two instances of firefox are fired up for the same user (+ profile) then what happens as far as I understand is another thread is started of the first process for the second instance. The way I would tackle replacing profile data with SQLite is to enforce single processes per profile with a lock file (I believe this is already done), and on start of (the first instance of) Firefox to fire up a special database thread that opens the database exclusively - thus ensuring no on-the-fly locking is required probably taking care of performance issues. Any accessing of the database by Firefox is then done by passing requests to this special thread (using mutexes, waits and signals and a global area), the thread then retrieves/updates the data and passes the result back to the calling thread. Although this is slightly more complex than otherwise, ot is not much more. It also should increase performance (no on-the-fly locking as only one connection is made), increases stability as passing multiple queries through a single connection in an embedded database is really a no-no and does the functions required. Yours Eddy Brett Wilson wrote: Hi everybody, I'm working on replacing a lot of Firefox's storage systems to use sqlite. It has been going well so far except for one issue. The database file is stored in the user's Mozilla profile directory. In companies and Universities using Linux, this directory is often hosted over the network. In these cases, sqlite performance can be quite poor. I am aware that writing performance can be bad in these cases, but we don't do too many writes. I am mostly concerned about read performance, since a lot of this stuff (history, bookmarks) drive the UI. The lag, even over a fast network, can be noticable. I am also concerned about file locking, since the documentation does not recommend using files over the network. Can anybody suggest what to do about this problem? This is not something that can be avoided, since some people will have this configuration and will not have any say about it. Firefox must perform reasonably in these cases. One thing that could work in our favor is that Mozilla already does locking on the profile, so access will be restricted to our one process. Is there anything that we can do to take advantage of this to avoid having to query the file for reads even when the content is cached? It looks like we will have multiple database connections from this process. I will work on minimizing the number of queries in the common cases, but any little bit of perfomance will help here. Thanks, Brett
Re: [sqlite] Network-based DB performance for Mozilla
Please excuse me doing another reply to this but Sorry about this daft question, but you have indexed everythink OK and designed the database to a reasonable BCNF(Boyce Codd Normal Form) model havn't you? Eddy Brett Wilson wrote: Hi everybody, I'm working on replacing a lot of Firefox's storage systems to use sqlite. It has been going well so far except for one issue. The database file is stored in the user's Mozilla profile directory. In companies and Universities using Linux, this directory is often hosted over the network. In these cases, sqlite performance can be quite poor. I am aware that writing performance can be bad in these cases, but we don't do too many writes. I am mostly concerned about read performance, since a lot of this stuff (history, bookmarks) drive the UI. The lag, even over a fast network, can be noticable. I am also concerned about file locking, since the documentation does not recommend using files over the network. Can anybody suggest what to do about this problem? This is not something that can be avoided, since some people will have this configuration and will not have any say about it. Firefox must perform reasonably in these cases. One thing that could work in our favor is that Mozilla already does locking on the profile, so access will be restricted to our one process. Is there anything that we can do to take advantage of this to avoid having to query the file for reads even when the content is cached? It looks like we will have multiple database connections from this process. I will work on minimizing the number of queries in the common cases, but any little bit of perfomance will help here. Thanks, Brett
Re: [sqlite] LIKE returns all rows
steve wrote: Ah, that works just fine. Is this noted someplace in the documentation that I missed? If not: - why does it work with single quotes and not double? - Shouldn't it be added? snip Double quotes and single quotes have different meanings in SQLite (as defined in ANSI SQL too). To produce string literals you MUST use single quotes. Double quotes are used to specify case sensitive field/table/etc names, or variable names with spaces in them. This is the same as Microsoft's SQLs [] characters, or MySQL backquote characters (neither are ANSI standard by the way). Therefore bob means the field name bob, and 'bob' means the literal bob. Eddy
Re: [sqlite] Proposal: limit the number of columns in a table to 2000.
I am an application programmer by trade, programming accountancy and inventory type packages using various SQLs. 2000 columns/tableis plenty for my use. I do not see needing to go above that in any scneario. If the need requires that you do need to go above that either the database design is wrong and needs redoing, or you should not really be using an engine like SQLite anyway! Eddy D. Richard Hipp wrote: As currently implemented, there is no fixed limit to the number of columns you can put in a table in SQLite. If the CREATE TABLE statement will fit in memory, then SQLite will accept it. Call the number of columns in a table K. I am proposing to limit the value of K to something like 2000. Would this cause anyone any grief? snip content=justification/
Re: [sqlite] SQLite3 and thread safety
What Platform are you on? For UNIX platforms there may be a problem, see the comments in the os_unix.c file in the source directory, or here http://www.sqlite.org/cvstrac/getfile/sqlite/src/os_unix.c?v=1.41 Though reading through things, wrapping arounb a MUTEX should be OK so long as there are no outstanding transactions (ie - you commit after inserting/updating/deleting a row and before releasing the mutex). Also - under UNIX - if you are wrapping it around a MUTEX, you may want to do a pthread_cond_wait instead (or as well I should say) as that is cancellable should the thing hang. Eddy Cory Nelson wrote: I understand you are supposed to open a new sqlite3 connection for every thread that needs access, but- would it be safe to wrap access to the database with a mutex? I've got code that needs to insert at random times (sometimes many times per sec) and select once per sec in another thread. The inserting can really get to CPU usage so i want to wrap it in a transaction that is closed/opened every time the select is performed. If there is a better way, I'm open to that too :)
Re: [sqlite] Does sqlite has isnull function?
[EMAIL PROTECTED] wrote: snip FYI: The function, as defined in ANSI, you are looking for is COALESCE select COALESCE(MAX(SubOrder), 0)+1 from Table1 where ... This is supported in sqlite3 (and I guess sqlite2) Eddy What I want to do is: select ISNULL(MAX(SubOrder), 0)+1 from Table1 where ... anyway I can do this, since isnull is not working this way. Thanks,
Re: [sqlite] what is in-memory SQLite database?
Naming the database ':memory:' (without the quotes) will not create a file but place the database in memory. Needless to say - it will not remember tables created their after disconnection, and will use RAM up :-) Eddy jack wu wrote: in the documentation: Appropriate Uses For SQLite it says: it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually i am wondering if there are more information/documentations on how to do that? i am writing an application where i need to define couple of huge hash tables. i load the data from disk, populate the hash tables and use the tables during execution. can i replace the hash tables using SQLite? i am new to SQLite, so any information is helpful. jack.
Re: [sqlite] ticket 1147
I use column names. I have created a wrapper around sqlite3 (and other SQL engines) in a developmeny environment I have written to enable the programmer (or user for that matter) to access an SQL result set using an object where the property names are the column names. However - having duplicate column names (without aliasing them), or using an unqualified * when querying a select statement with more than one table in it is really bad practice - and I do not think the ANSI standard specifies how that should be dealt with so I do not think sqlite CAN do it wrong in that case. Eddy D. Richard Hipp wrote: On Mon, 2005-02-28 at 11:12 +0200, Cariotoglou Mike wrote: I understand that this column names issue is becoming a pain for the sqlite authors, but OTOH, it is very important for wrapper authors... Why? Why does anybody care what the column names in the result are? What are the column names used for other than to print a header at the top of a table for human-readable output? Remember that not all wrappers are done by third parties. I ship the TCL wrapper with SQLite and for some reason I have never felt the need to turn on long_names or short_names. The default column names, whatever they might be, have generally been acceptable. So what is the difference? What are all these other wrappers doing that requires specific column names formats?
