Re: [sqlite] blob api
Ok, I was kinda hoping for a more permanent solution such as: Did you consider creating a view? int sqlite3_cast_column_type(sqlite3* db, const char* zDBname, const char* zTable, const char* zColumn, int newColumnType); that would cast the column into the desired type, returning SQLITE3_ERROR if the cast is invalid (like from double to integer, or text to numeric). You can use a CASE statement for this: select case typeof (a) when 'real' then a else 'Invalid type: ' || typeof (a) end from my_table; Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
I'm using the sqlite3_blob_* api to write a larger text stream incrementally. Works a charm, but is there a way to change the datatype of the blob to text afterwards ? I'd like to see the text easily in f.i. SQLiteSpy. Perhaps: SELECT CAST(b AS TEXT) FROM table Yes, this works well in SQLiteSpy. Really I suppose it depends on what SQLiteSpy is using to determine that the column type is BLOB. SQLiteSpy determines the type of each record cell individually, just as it is returned by sqlite3_column_type(). So casting a BLOB to text will display it as such in SQLiteSpy. In fact, SQLiteSpy colors the cell backgrounds by data type: White: Text Green: Integer Violet: Float Blue: Blob Red : Null Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Delphi dbExpress driver for SQLite3 ?
Hi All, I have downloaded dbExpress driver for SQLite3 from http://www.bcp-software.nl/artikelen/sqlite.html When I am using it under Delphi6 (also in Turbo Delphi Explorer) I receive often error Library used incorrectly ... I have uploaded reproducible test case (+sources - very simple) at http://www.mint.sk/download/sqlite_problem.zip Can anyone confirm that problem or provide some feedback , solution or link to other (free or LGPL) dbExpress (Delphi6) driver for SQLite3? TIA -Laco ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
Robert Bielik [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] D. Richard Hipp skrev: Perhaps: SELECT CAST(b AS TEXT) FROM table Ok, I was kinda hoping for a more permanent solution such as: int sqlite3_cast_column_type(sqlite3* db, const char* zDBname, const char* zTable, const char* zColumn, int newColumnType); Perhaps update tableName set b = cast(b as text); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blob api
Igor Tandetnik skrev: Ok, I was kinda hoping for a more permanent solution such as: int sqlite3_cast_column_type(sqlite3* db, const char* zDBname, const char* zTable, const char* zColumn, int newColumnType); Perhaps update tableName set b = cast(b as text); Hah! Of course! Thanks :) /R ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delphi dbExpress driver for SQLite3 ?
Try this: http://www.aducom.com/sqlite/ You can dump DBExpress completely. Fred -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of LacaK Sent: Tuesday, April 22, 2008 2:15 AM To: sqlite-users@sqlite.org Subject: [sqlite] Delphi dbExpress driver for SQLite3 ? Hi All, I have downloaded dbExpress driver for SQLite3 from http://www.bcp-software.nl/artikelen/sqlite.html When I am using it under Delphi6 (also in Turbo Delphi Explorer) I receive often error Library used incorrectly ... I have uploaded reproducible test case (+sources - very simple) at http://www.mint.sk/download/sqlite_problem.zip Can anyone confirm that problem or provide some feedback , solution or link to other (free or LGPL) dbExpress (Delphi6) driver for SQLite3? TIA -Laco ___ 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] restricting access to sqlite database
Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Thanks in advance for any help, Thomas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Nothing specific to SQLite, but common good web programming practice. Don't keep the db in a web accessible path. My web root is /path/to/web/root/ my db is in /a/totally/different/path/to/db -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
Thanks for your reply! However, apache has to be able to access /a/ totally/different/path/to/db, so this means that any user on the same server can access it via e.g. a PHP web page, if they know that path, is that correct? Thomas On 22 Apr 2008, at 15:14, P Kishor wrote: On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Nothing specific to SQLite, but common good web programming practice. Don't keep the db in a web accessible path. My web root is /path/to/web/root/ my db is in /a/totally/different/path/to/db -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ 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] restricting access to sqlite database
On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Thanks for your reply! However, apache has to be able to access /a/totally/different/path/to/db, so this means that any user on the same server can access it via e.g. a PHP web page, if they know that path, is that correct? Just because apache the user account on your compute can access the db, doesn't mean apache the webserver is serving that file. My webserver runs as user www My db is under ~/Data/website/database.db owned by me, but chmod-ed to 666 The webserver serves only files under ~/Sites/website/ Thomas On 22 Apr 2008, at 15:14, P Kishor wrote: On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Nothing specific to SQLite, but common good web programming practice. Don't keep the db in a web accessible path. My web root is /path/to/web/root/ my db is in /a/totally/different/path/to/db -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
Just because apache the user account on your compute can access the db, doesn't mean apache the webserver is serving that file. My webserver runs as user www My db is under ~/Data/website/database.db owned by me, but chmod- ed to 666 The webserver serves only files under ~/Sites/website/ I understand what you mean, but if your database file is chmod-ed to 666, any other user logged in to your web server can edit it, correct? If you are the only user on your web server, then indeed placing it outside the web directory is enough, but what I am asking about is for cases when there are 100 or 1000 users that can all log in to the same web server. Thomas Thomas On 22 Apr 2008, at 15:14, P Kishor wrote: On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Nothing specific to SQLite, but common good web programming practice. Don't keep the db in a web accessible path. My web root is /path/to/web/root/ my db is in /a/totally/different/path/to/db -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http:// www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
Thanks for your reply! However, apache has to be able to access /a/ totally/different/path/to/db, so this means that any user on the same server can access it via e.g. a PHP web page, if they know that path, is that correct? Yes, but In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? MySQL would actually suffer from a similar problem but in a different way. Imagine the scenario that your forum accesses a MySQL database using username password strings stored in a PHP script. This script would need to be readable by apache for the forum to work. If someone else know the name of that script, they could craft a rogue PHP to display the above PHP script so that they could copy the username/password. They could use username/password to access your MySQL database and corrupt/delete it. There are alternative solutions. One I know of (but never used before) is to use the 'cgi' version of PHP which can run under different user names. Best place to ask would be a PHP list. 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. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] restricting access to sqlite database
I'm assuming you're using mod_php. For a virtual-hosted environment, I don't think that can be made 'secure'. You probably need to switch to suexec and fastcgi php. That way the php scripts are run as your user rather than the 'www' user. You might glance at this: http://www.k4ml.com/wiki/server/apache/php-fastcgi -Brad Thomas Robitaille wrote: Just because apache the user account on your compute can access the db, doesn't mean apache the webserver is serving that file. My webserver runs as user www My db is under ~/Data/website/database.db owned by me, but chmod- ed to 666 The webserver serves only files under ~/Sites/website/ I understand what you mean, but if your database file is chmod-ed to 666, any other user logged in to your web server can edit it, correct? If you are the only user on your web server, then indeed placing it outside the web directory is enough, but what I am asking about is for cases when there are 100 or 1000 users that can all log in to the same web server. Thomas Thomas On 22 Apr 2008, at 15:14, P Kishor wrote: On 4/22/08, Thomas Robitaille [EMAIL PROTECTED] wrote: Hi everyone, I am in the process of setting up a forum which uses SQLite on a web server which has ~50 other users. I can create a directory for the sqlite database, which I chown to 'apache' (the user under which the web server is run). However, because the database is then writable by apache, could other users not potentially write web applications which could edit that database (and potentially remove all tables?). In MySQL for example, this is not a problem because of the different users/privileges, but what is the common way around this in SQLite? Nothing specific to SQLite, but common good web programming practice. Don't keep the db in a web accessible path. My web root is /path/to/web/root/ my db is in /a/totally/different/path/to/db -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http:// www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ 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] Creating a Database from RealBasic code
This should be simple but does anyone know how to create a new database from within realbasic code? I try to shell out using the shell command but it's not liking that too much and there seems to be no create database function for sqlite. Dim s As Shell s= New Shell f = GetFolderItem() appPath=f.AbsolutePath 'appPath=app.ExecutableFile.Parent.AbsolutePath msgbox appPath #if TargetWin32 s.execute sqlite3.exe testdatabase.db 's.execute dir #endif If s.errorCode = 0 then msgbox done. else MsgBox Error code: + Str(s.errorCode) + chr(13) + s.result end if I get: Error code -2 Shell time out. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] OMIT_VIEW / TRIGGER bug?
This was a problem for me too. I just chalked it up to deleting options
and using the amalgamated source. (Seem to recall reading that this is
not recommended.)
Replace:
SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
int);
with:
#if !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER)
SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
int);
#else /* #if !defined(SQLITE_OMIT_VIEW)
!defined(SQLITE_OMIT_TRIGGER) */
# define sqlite3MaterializeView(A,B,C,D,E) 0
#endif /* #if !defined(SQLITE_OMIT_VIEW)
!defined(SQLITE_OMIT_TRIGGER) */
or equivalent. As I recall, this function is called from within an if
block whose condition (because of the #defines) will never be true. For
those of us who must live with the MSFT compilers, this is a problem.
Richard Klein wrote:
I fixed my OPTS in the Makefile so that they are in sync
with my compilation options.
Now all the unresolved references in the parser have dis-
appeared, but I'm still left with two unresolved references
to the function sqlite3MaterializeView():
delete.obj : error LNK2019: unresolved external symbol
_sqlite3MaterializeView referenced in function _sqlite3DeleteFrom
update.obj : error LNK2019: unresolved external symbol
_sqlite3MaterializeView referenced in function _sqlite3Update
The function sqlite3MaterializeView() is defined in the
file delete.c, as follows:
#if !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER)
/*
** Evaluate a view and store its result in an ephemeral table. The
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
...
){
...
}
#endif /* !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER) */
In my application, I've defined SQLITE_OMIT_VIEW, but *not*
SQLITE_OMIT_TRIGGER; that is, I want TRIGGERs, but not VIEWs.
It would seem that in the conditional compilation expression
shown above, the should be replaced by ||:
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
In other words, if VIEW *or* TRIGGER is supported, then define
the function sqlite3MaterializeView().
Making that change fixes the problem.
- Richard
___
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] Creating a Database from RealBasic code
Hi Thomas, No need to shell out, you create from within the realbasic application. I'll pluck some example code and email it to you. Regards Denis On 04/23/2008 12:44 AM, Thomas E. Wright wrote: This should be simple but does anyone know how to create a new database from within realbasic code? I try to shell out using the shell command but it's not liking that too much and there seems to be no create database function for sqlite. Dim s As Shell s= New Shell f = GetFolderItem() appPath=f.AbsolutePath 'appPath=app.ExecutableFile.Parent.AbsolutePath msgbox appPath #if TargetWin32 s.execute sqlite3.exe testdatabase.db 's.execute dir #endif If s.errorCode = 0 then msgbox done. else MsgBox Error code: + Str(s.errorCode) + chr(13) + s.result end if I get: Error code -2 Shell time out. ___ 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] Creating a Database from RealBasic code
Thanks Denis. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Denis Crowther Sent: Tuesday, April 22, 2008 10:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Creating a Database from RealBasic code Hi Thomas, No need to shell out, you create from within the realbasic application. I'll pluck some example code and email it to you. Regards Denis On 04/23/2008 12:44 AM, Thomas E. Wright wrote: This should be simple but does anyone know how to create a new database from within realbasic code? I try to shell out using the shell command but it's not liking that too much and there seems to be no create database function for sqlite. Dim s As Shell s= New Shell f = GetFolderItem() appPath=f.AbsolutePath 'appPath=app.ExecutableFile.Parent.AbsolutePath msgbox appPath #if TargetWin32 s.execute sqlite3.exe testdatabase.db 's.execute dir #endif If s.errorCode = 0 then msgbox done. else MsgBox Error code: + Str(s.errorCode) + chr(13) + s.result end if I get: Error code -2 Shell time out. ___ 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] OMIT_VIEW / TRIGGER bug?
While your solution will eliminate the link errors, I think that my
solution -- replacing with || in the conditional expression -- is
actually the correct one, for two reasons:
(1) In the source code, every other conditional expression involving
OMIT_VIEW and OMIT_TRIGGER uses not but ||.
(2) Logically, specifying OMIT_VIEW should be enough to omit sqlite3-
MaterializeView() -- I shouldn't have to additionally specify OMIT_
TRIGGER.
- Richard Klein
Mark Spiegel wrote:
This was a problem for me too. I just chalked it up to deleting options
and using the amalgamated source. (Seem to recall reading that this is
not recommended.)
Replace:
SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
int);
with:
#if !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER)
SQLITE_PRIVATE void sqlite3MaterializeView(Parse*, Select*, Expr*, u32,
int);
#else /* #if !defined(SQLITE_OMIT_VIEW)
!defined(SQLITE_OMIT_TRIGGER) */
# define sqlite3MaterializeView(A,B,C,D,E) 0
#endif /* #if !defined(SQLITE_OMIT_VIEW)
!defined(SQLITE_OMIT_TRIGGER) */
or equivalent. As I recall, this function is called from within an if
block whose condition (because of the #defines) will never be true. For
those of us who must live with the MSFT compilers, this is a problem.
Richard Klein wrote:
I fixed my OPTS in the Makefile so that they are in sync
with my compilation options.
Now all the unresolved references in the parser have dis-
appeared, but I'm still left with two unresolved references
to the function sqlite3MaterializeView():
delete.obj : error LNK2019: unresolved external symbol
_sqlite3MaterializeView referenced in function _sqlite3DeleteFrom
update.obj : error LNK2019: unresolved external symbol
_sqlite3MaterializeView referenced in function _sqlite3Update
The function sqlite3MaterializeView() is defined in the
file delete.c, as follows:
#if !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER)
/*
** Evaluate a view and store its result in an ephemeral table. The
** pWhere argument is an optional WHERE clause that restricts the
** set of rows in the view that are to be added to the ephemeral table.
*/
void sqlite3MaterializeView(
...
){
...
}
#endif /* !defined(SQLITE_OMIT_VIEW) !defined(SQLITE_OMIT_TRIGGER) */
In my application, I've defined SQLITE_OMIT_VIEW, but *not*
SQLITE_OMIT_TRIGGER; that is, I want TRIGGERs, but not VIEWs.
It would seem that in the conditional compilation expression
shown above, the should be replaced by ||:
#if !defined(SQLITE_OMIT_VIEW) || !defined(SQLITE_OMIT_TRIGGER)
In other words, if VIEW *or* TRIGGER is supported, then define
the function sqlite3MaterializeView().
Making that change fixes the problem.
- Richard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger on an attached db.
Hi, I hope this question isn't a noob one like my last one ... I have two db, the main one is used from mine sw (call this db A), the other is used from another sw (call this db B). I open A, attach B, create a temp trigger in A triggered by insert into a table in B and writing in a table in A If I insert into the table in B the trigger is triggered but if the sw (working on B) insert the same thing in the same table the trigger do nothing. db A CREATE TABLE original(id integer primary key, data text); db B attach database 'A.db' as A; CREATE TABLE sync(id integer primary key, original_id integer, flag integer); CREATE TRIGGER to_sync after insert on A.original begin insert into sync (original_id,flag) values(new.id,1); end; running from B sqlite insert into original(data) values(test); sqlite select * from original; 1|test sqlite select * from sync; 1|1|1 if I open another instance of sqlite3 for A running another insert sqlite insert into original (data) values(test2); in B I see sqlite select * from original; 1|test 2|test2 sqlite select * from sync; 1|1|1 It's possible to get the trigger run when insert is lunched from another session ? -- [image: Just A Little Bit Of Geekness]http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1 Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza. (Larry Wall). ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to retrieve number of cached pages in memory?
I need to retrieve the number of pages a SQLite database connection has currently allocated in memory. The documentation unfortunately turned up no results. I know about PRAGMA cache_size, but this returns the maximum number of pages possibly allowed in the cache, not the actual number of pages currently cached. My aim is to calculate the accurate number of bytes actually consumed by a single cached page. This figure will then allow to set PRAGMA cache_size to a more precise value in order to limit memory usage. I do mind using undocumented APIs and will not cry tears if they change without notice, so any pointers are welcome! Many thanks, Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger on an attached db.
Federico Granata [EMAIL PROTECTED] wrote: I have two db, the main one is used from mine sw (call this db A), the other is used from another sw (call this db B). I open A, attach B, create a temp trigger in A triggered by insert into a table in B and writing in a table in A If I insert into the table in B the trigger is triggered but if the sw (working on B) insert the same thing in the same table the trigger do nothing. Each connection has its own independent temp database. You've created a temporary trigger which exists in the temp database for your connection. The trigger simply doesn't exist on the other connection. It's possible to get the trigger run when insert is lunched from another session ? Yes, if that other session attaches the appropriate database and creates an appropriate trigger. No, you cannot magically alter the behavior of another application you have no control over. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
You can call sqlite3_memory_highwater() to find the maximum amount of memory (in bytes) that your app has used. Then you can divide this number by 2000 (the default size of a page) to determine the max number of pages that have been cached. - Richard Klein Ralf Junker wrote: I need to retrieve the number of pages a SQLite database connection has currently allocated in memory. The documentation unfortunately turned up no results. I know about PRAGMA cache_size, but this returns the maximum number of pages possibly allowed in the cache, not the actual number of pages currently cached. My aim is to calculate the accurate number of bytes actually consumed by a single cached page. This figure will then allow to set PRAGMA cache_size to a more precise value in order to limit memory usage. I do mind using undocumented APIs and will not cry tears if they change without notice, so any pointers are welcome! Many thanks, Ralf ___ 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] How to retrieve number of cached pages in memory?
On Wed, Apr 23, 2008 at 12:19:45AM +0200, Ralf Junker scratched on the wall: I need to retrieve the number of pages a SQLite database connection has currently allocated in memory. The documentation unfortunately turned up no results. I know about PRAGMA cache_size, but this returns the maximum number of pages possibly allowed in the cache, not the actual number of pages currently cached. My aim is to calculate the accurate number of bytes actually consumed by a single cached page. This figure will then allow to set PRAGMA cache_size to a more precise value in order to limit memory usage. I do mind using undocumented APIs and will not cry tears if they change without notice, so any pointers are welcome! See the Pager data structure and associated variables and functions in sqlite-3.5.x/src/pager.c. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
On Tue, Apr 22, 2008 at 03:33:27PM -0700, Richard Klein scratched on the wall: You can call sqlite3_memory_highwater() to find the maximum amount of memory (in bytes) that your app has used. Then you can divide this number by 2000 (the default size of a page) to determine the max number of pages that have been cached. The default page size is 1024 bytes. The default cache size (in pages) is 2000. (At least according to http://www.sqlite.org/compile.html) -j -- Jay A. Kreibich J A Y @ K R E I B I.C H 'People who live in bamboo houses should not throw pandas.' Jesus said that. - The Ninja, www.AskANinja.com, Special Delivery 10: Pop!Tech 2006 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Writer Starvation Question
I am using SQLite 3.5.7. This is a simplified example, but I have 2 threads in the same process, each with their own connection, and cache sharing disabled. Thread A does: while (some condtion) BEGIN IMMEDIATE do some INSERTs COMMIT Thread B occasionally wants to also do its own BEGIN IMMEDIATE...COMMIT I found that thread A tends to hog the database: thread B often times out when attempting to begin its transaction. I added a sleep of 2 seconds at the bottom of thread A's loop, to give thread B a chance to get in. That worked, but of course the throughput of thread A is reduced as a result of the sleep calls, even when thread B doesn't want to use the db. Is there a better mechanism I should employ to ensure one writer doesn't hog the database? Thanks, Dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to retrieve number of cached pages in memory?
You're right! A grep of the source code confirms that. My mistake. - Richard Jay A. Kreibich wrote: On Tue, Apr 22, 2008 at 03:33:27PM -0700, Richard Klein scratched on the wall: You can call sqlite3_memory_highwater() to find the maximum amount of memory (in bytes) that your app has used. Then you can divide this number by 2000 (the default size of a page) to determine the max number of pages that have been cached. The default page size is 1024 bytes. The default cache size (in pages) is 2000. (At least according to http://www.sqlite.org/compile.html) -j ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writer Starvation Question
Weirdo response, but ... make sure you have HAVE_USLEEP defined. We kept seeing something similar, and kept looking at the code for sqliteDefaultBusyCallback(), and the code looked right, but the problem remained, until we eventually started questioning every assumption. At which point we found that we needed to add -DHAVE_USLEEP=1 somewhere. Also, you should note your platform. Anyhow, another solution would naturally be to use a mutex to prevent multiple writers from getting in. Depending on your app's structure, this can be pretty annoying to add on top... -scott On Tue, Apr 22, 2008 at 3:44 PM, Fin Springs [EMAIL PROTECTED] wrote: I am using SQLite 3.5.7. This is a simplified example, but I have 2 threads in the same process, each with their own connection, and cache sharing disabled. Thread A does: while (some condtion) BEGIN IMMEDIATE do some INSERTs COMMIT Thread B occasionally wants to also do its own BEGIN IMMEDIATE...COMMIT I found that thread A tends to hog the database: thread B often times out when attempting to begin its transaction. I added a sleep of 2 seconds at the bottom of thread A's loop, to give thread B a chance to get in. That worked, but of course the throughput of thread A is reduced as a result of the sleep calls, even when thread B doesn't want to use the db. Is there a better mechanism I should employ to ensure one writer doesn't hog the database? Thanks, Dave ___ 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] Writer Starvation Question
Fin Springs [EMAIL PROTECTED] wrote: I am using SQLite 3.5.7. This is a simplified example, but I have 2 threads in the same process, each with their own connection, and cache sharing disabled. Thread A does: while (some condtion) BEGIN IMMEDIATE do some INSERTs COMMIT Thread B occasionally wants to also do its own BEGIN IMMEDIATE...COMMIT I found that thread A tends to hog the database: thread B often times out when attempting to begin its transaction. I added a sleep of 2 seconds at the bottom of thread A's loop, to give thread B a chance to get in. That worked, but of course the throughput of thread A is reduced as a result of the sleep calls, even when thread B doesn't want to use the db. Have thread B let thead A know that it wants in by using some synchronization primitive. E.g on Windows I'd use a manual reset event. Thread A waits on the event at the top of the loop. The event is set (signalled) most of the time, so the wait doesn't block. But when thread B wants to write, it resets the event, then tries to start the transaction. On the next iteration, thread A would block on the event, and thread B would go through. When it's done, it will set the event back, which will wake up thread A. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writer Starvation Question
On Apr 22, 2008, at 6:52 PM, Scott Hess shess-at-google.com |sqlite| wrote: Weirdo response, but ... make sure you have HAVE_USLEEP defined. We kept seeing something similar, and kept looking at the code for sqliteDefaultBusyCallback(), and the code looked right, but the problem remained, until we eventually started questioning every assumption. At which point we found that we needed to add -DHAVE_USLEEP=1 somewhere. Also, you should note your platform. Anyhow, another solution would naturally be to use a mutex to prevent multiple writers from getting in. Depending on your app's structure, this can be pretty annoying to add on top... -scott Sorry, I should have said that this is on Windows XP. I considered using a mutex but since transactions do their own mutexing it seemed redundant to layer my own on top. I also thought I had read somewhere on sqlite.org that SQLite had write-starvation improvements in recent releases, so I wanted to make sure I wasn't missing something obvious. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writer Starvation Question
On Apr 22, 2008, at 6:52 PM, Igor Tandetnik itandetnik-at-mvps.org | sqlite| wrote: Have thread B let thead A know that it wants in by using some synchronization primitive. E.g on Windows I'd use a manual reset event. Thread A waits on the event at the top of the loop. The event is set (signalled) most of the time, so the wait doesn't block. But when thread B wants to write, it resets the event, then tries to start the transaction. On the next iteration, thread A would block on the event, and thread B would go through. When it's done, it will set the event back, which will wake up thread A. Igor Tandetnik Thanks Igor. I gave a simplified example; I actually have 6 threads and they're in C++ but the code that they're actually running that uses the db is in Lua, so the C++ doesn't know at compile time which threads are likely to be annoying and do transactions in a loop. However, I could expose event functions to Lua from C++. I was hoping that I was missing something and that SQLite would manage this internally, but presuming I'm not missing some magic SQLITE_DONTOMIT_xxx definition in the amalgamation, the event seems like a good way to go. Thank you for the suggestion. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to execute the statment file using sqlite API
Hi all, I have the serveral sql statement in the one file call : getData.sql and I want to use the sqlite API to call this file to execute all sql statements in this file at once. Can you tell me what API I should use. I had the code to execute the statment but I don't know how to call the file. Below is my code to execute the single statement. sqlSt = sqlite3_open( name, pDb); strcpy(stmt[0], Select * from my table); sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, errMsg) ; Thanks, JL Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writer Starvation Question
Hello Fin, It sounds like you're creating inserts faster than you can actually insert them in the DB so, things are backing up. In this case, it's the second worker that's backing up. I'd use a single worker thread that accepts DB requests from the other worker threads then queue jobs to the single DB worker. If one thread is being starved, that means the bottleneck is likely disk IO and not CPU. In that case, it's probably inefficient to use muliple writers to the DB. It also means the single DB worker can use transactions more efficiently if it declares a transaction before working on the queue of insertion jobs. C Tuesday, April 22, 2008, 6:44:33 PM, you wrote: FS I am using SQLite 3.5.7. This is a simplified example, but I have 2 FS threads in the same process, each with their own connection, and cache FS sharing disabled. FS Thread A does: FS while (some condtion) FS BEGIN IMMEDIATE FS do some INSERTs FS COMMIT FS Thread B occasionally wants to also do its own BEGIN IMMEDIATE...COMMIT FS I found that thread A tends to hog the database: thread B often times FS out when attempting to begin its transaction. I added a sleep of 2 FS seconds at the bottom of thread A's loop, to give thread B a chance to FS get in. That worked, but of course the throughput of thread A is FS reduced as a result of the sleep calls, even when thread B doesn't FS want to use the db. FS Is there a better mechanism I should employ to ensure one writer FS doesn't hog the database? FS Thanks, FS Dave FS ___ FS sqlite-users mailing list FS sqlite-users@sqlite.org FS http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLITE_OMIT_PAGER_PRAGMAS
I've generated and compiled the SQLite sources with the option SQLITE_OMIT_PAGER_PRAGMAS. If I call sqlite_prepare() and sqlite3_step() on the SQL statement PRAGMA cache_size = 100;, I get return codes of SQLITE_OK and SQLITE_DONE, respectively, but the cache_size doesn't seem to change. Is this the correct behavior? If so, fine -- it's just that I would have expected sqlite3_ prepare() to return an error code. - Richard Klein ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_OMIT_PAGER_PRAGMAS
On Apr 22, 2008, at 8:37 PM, Richard Klein wrote: I've generated and compiled the SQLite sources with the option SQLITE_OMIT_PAGER_PRAGMAS. If I call sqlite_prepare() and sqlite3_step() on the SQL statement PRAGMA cache_size = 100;, I get return codes of SQLITE_OK and SQLITE_DONE, respectively, but the cache_size doesn't seem to change. Is this the correct behavior? If so, fine -- it's just that I would have expected sqlite3_ prepare() to return an error code. When you compile with SQLITE_OMIT_PAGER_PRAGMAS the cache_size pragma is omitted. A feature of the PRAGMA command is that unrecognized pragmas are silently ignored. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delphi dbExpress driver for SQLite3 ?
Thank you Fred, but I need multi-DB connectivity. User can set up in ini file, which driver use. Hm. -Laco. Try this: http://www.aducom.com/sqlite/ You can dump DBExpress completely. Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
