Re: [sqlite] Trigger on an attached db.
On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > 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. > damn ... 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. > I don't want to alter the behavior of the other app nor I want to alter his db. If I create mine table and trigger in the B db everything run smooth but I try to "hook" on some event without disturbing the original behavior. It's possible at all ? Tnx. ___ 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?
> See the "Pager" data structure and associated variables and functions > in "sqlite-3.5.x/src/pager.c". OK, it seems that the number I am interested in is stored as part of the Pager struct: int nPage; /* Total number of in-memory pages */ Now I just have to figure out how to access this number reliably. Many thanks, Ralf ___ 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
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
[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] 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] 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
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
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
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
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] 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
[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?
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
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?
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] 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
[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
[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]
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] Delete inside Select+Step - Reposted
I posted this earlier in the week and didn't get a resolution. Can
anyone help out?
---
Using the latest SQLite Version can you do:
select clma from tablea;
while( sqlite3_step() )
{
somevalue = sqlite3_column_int( clma );
if ( somefunction( somevalue ) == true )
delete from tablea clma=somevalue;
else
process_row_as_required;
}
ie. Delete a row while stepping through the results of a select and
know the remaining sqlite3_step()'s will work correctly?
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
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
Re: [sqlite] sqlite DB on a CD
[EMAIL PROTECTED] wrote: > Hi, > I like to distribute my application on a CD. > This application has a sqlite database which will be part of the > distribution. > I do not like anybody reading/accesing the database directly, so I put > the database file inside a zip file which is password protected. > My application is written in VB, and I have hard coded the password > inside the VB program. > When the application loads, it opens the password protected zip file, > and extracts the sqlite file within it. > Now, I do not want to write this db file to any temporary location on > the hard disk. Is it possible to load the entire sqlite file in memory > and the application read data from it? If your database is so small that you would want to read the whole thing into RAM from a zip file before each use, then you could instead avoid distributing a SQLite file instead, but rather put all the SQL create/insert statements for it in your application binary instead, and then when the application starts up, create a memory-only SQLite database and run those SQL to populate it. If you still want to distribute your data as a SQLite file, then I suggest instead you pay D Hipp's fee for the SQLite with built-in encryption and then you can just distribute encrypted SQLite databases and use them off disk, with no need to load it all into RAM to protect your data; this approach is also a lot faster performing if your data set is large. If you're just worried about people modifying the database and not just reading it directly, well a CD is a read-only medium, if they run the program straight off of it. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite DB on a CD
Hi, I like to distribute my application on a CD. This application has a sqlite database which will be part of the distribution. I do not like anybody reading/accesing the database directly, so I put the database file inside a zip file which is password protected. My application is written in VB, and I have hard coded the password inside the VB program. When the application loads, it opens the password protected zip file, and extracts the sqlite file within it. Now, I do not want to write this db file to any temporary location on the hard disk. Is it possible to load the entire sqlite file in memory and the application read data from it? -Shibu e-mail: [EMAIL PROTECTED] DISCLAIMER: This message contains privileged and confidential information and is intended only for an individual named. If you are not the intended recipient, you should not disseminate, distribute, store, print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete or contain viruses. The sender, therefore, does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required, please request a hard-copy version. ___ 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] 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] 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
[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] 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//database.db owned by me, but chmod- >> ed to 666 >> >> The webserver serves only files under ~/Sites// > > 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
Re: [sqlite] restricting access to sqlite database
On 4/22/08, Thomas Robitaille <[EMAIL PROTECTED]> 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//database.db owned by me, but chmod-ed to > 666 > > > > The webserver serves only files under ~/Sites// > > > > 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. Why on earth do your web users log on to your web server from any interface other than the web? My web server is behind a firewall, reachable only via ssh over vpn. > > 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 > > > > > > > > > > > > ___ 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
> 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//database.db owned by me, but chmod- > ed to 666 > > The webserver serves only files under ~/Sites// 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
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//database.db owned by me, but chmod-ed to 666 The webserver serves only files under ~/Sites// > > 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? 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: > 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] 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] 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
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] 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
[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
