Re: [sqlite] Journal file question

[DragonK]

On 4/19/07, Joe Wilson <[EMAIL PROTECTED]> wrote:


--- DragonK <[EMAIL PROTECTED]> wrote:
> I'm having the following problem:  a sqlite database file is on an NTFS
> filesystem, in a directory with no permissions to create new files, but
only
> to modify the original database. By using filemon i've noticed some
access
> denied errors when sqlite attempted to create the journal files.
> I've created a sepparate test case and (by using filemon again) i've
noticed
> that indeed, sqlite uses the journal file, even outside transactions (an
> insert sql was executed).
>
> My question is how can I stop this behaviour (creating/deleting the
journal)
> so that sqlite will work properly under the scenario described above
(when
> it can't create the journal)?

No problem - just create your own virtual file system in a file and
change sqlite's I/O functions:

/*
** An instance of the following structure contains pointers to all
** methods on an OsFile object.
*/
struct IoMethod {
  int (*xClose)(OsFile**);
  int (*xOpenDirectory)(OsFile*, const char*);
  int (*xRead)(OsFile*, void*, int amt);
  int (*xWrite)(OsFile*, const void*, int amt);
  int (*xSeek)(OsFile*, i64 offset);
  int (*xTruncate)(OsFile*, i64 size);
  int (*xSync)(OsFile*, int);
  void (*xSetFullSync)(OsFile *id, int setting);
  int (*xFileHandle)(OsFile *id);
  int (*xFileSize)(OsFile*, i64 *pSize);
  int (*xLock)(OsFile*, int);
  int (*xUnlock)(OsFile*, int);
  int (*xLockState)(OsFile *id);
  int (*xCheckReservedLock)(OsFile *id);
  int (*xSectorSize)(OsFile *id);
};

I don't know of any other way given your constraints.

See also: Single-file virtual file systems
http://en.wikipedia.org/wiki/Virtual_file_system


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





Thanks everybody for answering. I'm going to move the database in a
directory with the proper permissions...






--
...it's only a matter of time...

Re: [sqlite] Journal file question

[DragonK]

On 4/18/07, Cesar Rodas <[EMAIL PROTECTED]> wrote:


I think is imposible... what you need to do is to create a
dbname.db-journalform a
dbname.db and set permission to truncate, write, but not for delete.. that
is what i suggest, but i didnt try it...



If you're suggesting to create a dummy journal file with all but delete
permissions on it, I think that'd cause more problems, since sqlite will see
the journal there and it will try to roll it back every time I use the
database... :(


On 18/04/07, DragonK <[EMAIL PROTECTED]> wrote:

>
> Hi,
>
> I'm having the following problem:  a sqlite database file is on an NTFS
> filesystem, in a directory with no permissions to create new files, but
> only
> to modify the original database. By using filemon i've noticed some
access
> denied errors when sqlite attempted to create the journal files.
> I've created a sepparate test case and (by using filemon again) i've
> noticed
> that indeed, sqlite uses the journal file, even outside transactions (an
> insert sql was executed).
>
> My question is how can I stop this behaviour (creating/deleting the
> journal)
> so that sqlite will work properly under the scenario described above
(when
> it can't create the journal)?
>
> PS: Yes, I know, I'm still google-ing, but I'm not having much luck,
> that's
> why I'm posting here..
>
> Thanks!
>
>
>
> --
> ...it's only a matter of time...
>



--
Cesar Rodas
http://www.cesarodas.com/
Mobile Phone: 595 961 974165
Phone: 595 21 645590
[EMAIL PROTECTED]
[EMAIL PROTECTED]





--
...it's only a matter of time...

[sqlite] Journal file question

[DragonK]

Hi,

I'm having the following problem:  a sqlite database file is on an NTFS
filesystem, in a directory with no permissions to create new files, but only
to modify the original database. By using filemon i've noticed some access
denied errors when sqlite attempted to create the journal files.
I've created a sepparate test case and (by using filemon again) i've noticed
that indeed, sqlite uses the journal file, even outside transactions (an
insert sql was executed).

My question is how can I stop this behaviour (creating/deleting the journal)
so that sqlite will work properly under the scenario described above (when
it can't create the journal)?

PS: Yes, I know, I'm still google-ing, but I'm not having much luck, that's
why I'm posting here..

Thanks!



--
...it's only a matter of time...

[sqlite] Database security

[DragonK]

Hi,

I'm wondering if there's any free addon for sqlite which would add a
transparent encryption layer to the database... can anyone provide any
information?

Thanks!

--
...it's only a matter of time...

[sqlite] SQLite and text encoding

[DragonK]

Hi,

I have a table containing a text field, which is UTF-8 encoded data.  I want
to know if I use sqlite3_column_text16() to retrieve the contents
of the field, would the data be automatically converted to UTF-16, or would
the characters be simply "extended" to 16 bit ? The documentation didn't
help me much

Any solutions/hints are deeply appreciated!  :)

Thanks.

--
...it's only a matter of time...

Re: [sqlite] Select and deletion

[DragonK]

On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


DragonK <[EMAIL PROTECTED]> wrote:
>
> So, as I understand it, this is supported from version 3.3.8 upwards,
right?
>

Correct.



Thanks for  straigthening out the issue! :)




--

D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
...it's only a matter of time...

Re: [sqlite] Select and deletion

[DragonK]

On 2/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"Jay Sprenkle" <[EMAIL PROTECTED]> wrote:
> On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:
> >
> >
> > This seems to work... but I'm not sure if it should, that's why I'm
> > asking.
> > I hope the code will show more exactly what I intend to do. Sorry for
the
> > lack of proper error checking, it's just a test program. :) Also, I'm
not
> > using any transactions, just the PRAGMA synchronous= off;
>
> If it works that's great. I would double check that it did work
correctly.
> I'll have to poke around now and see why what I did, which was pretty
> similar, didn't work.
>

Perhaps what Jay did occurred before [3355] (first released with 3.3.8)
and what DragonK did occurred afterwards.

  http://www.sqlite.org/cvstrac/chngview?cn=3355




So, as I understand it, this is supported from version 3.3.8 upwards, right?



--

D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
...it's only a matter of time...

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/8/07, Dennis Cote <[EMAIL PROTECTED]> wrote:


DragonK wrote:
>
>
> Well, using transactions would be a little difficult, because I have a
> library which does something like logging to a database. Using
> transaction
> on a single insert would be useless in my opinion, and on the other
> hand I
> can't use transactions on the all inserts, since logging is a continuous
> process, it doesn't end.
>
You can basically have a transaction open all the time. Begin a
transaction on startup. Insert your log entries as they happen. Execute
a commit and another begin after every 100 records, and commit when your
program ends. You can also get more sophisticated adding a timer that
will also do a commit and begin say one second after the last log entry
if you need to. You will get a speedup by nearly a factor of 100, and if
you have a power failure or OS crash you will only lose  the uncommitted
records, not your entire database.

HTH
Dennis Cote



Thanks for your reply, Dennis -  I'll consider this alternative.




--
...it's only a matter of time...

Re: [sqlite] Select and deletion

[DragonK]

On 2/8/07, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 2/7/07, DragonK <[EMAIL PROTECTED]> wrote:
>
>
> This seems to work... but I'm not sure if it should, that's why I'm
> asking.
> I hope the code will show more exactly what I intend to do. Sorry for
the
> lack of proper error checking, it's just a test program. :) Also, I'm
not
> using any transactions, just the PRAGMA synchronous= off;



If it works that's great. I would double check that it did work correctly.
I'll have to poke around now and see why what I did, which was pretty
similar, didn't work.




Please inform me if you find anything wrong with this scenario, because I
want to use something similar in production code.

Also, I'd appreciate any other opinions if some of you tried something
similar and had good/bad results.

I don't know the internals of SQLite or of databases in general, but in this
case i think the database knows the next row to be selected and deleting the
currently selected row would not interfere with the select operation i
think :)

Thanks!



--

The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com





--
...it's only a matter of time...

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, John Stanton <[EMAIL PROTECTED]> wrote:


DragonK wrote:
> On 2/7/07, Ken <[EMAIL PROTECTED]> wrote:
>
>>
>> Try without the pragma and wrap the inserts with a begin transaction
>> and a
>> commit...
>>
>>   The performance will be almost as good as with the pragma, with the
>> added benefit of consistent data and no corruption in the event of a
>> crash
>> or power failure.
>
>
>
> I know, but in my case, I can't use transactions due to the architecture
of
> the product.

Why not?  Can't you just execute SQL?



Well, using transactions would be a little difficult, because I have a
library which does something like logging to a database. Using transaction
on a single insert would be useless in my opinion, and on the other hand I
can't use transactions on the all inserts, since logging is a continuous
process, it doesn't end.





>
>
> DragonK <[EMAIL PROTECTED]> wrote:
>
>>   On 2/7/07, Teg wrote:
>> >
>> > Hello ohadp,
>> >
>> > Well, my experience is, particularly when it's users using it and not
>> > a personal project, that corruption happens fairly frequently when
you
>> > use this pragma. That's why I don't use it any more in my production
>> > code.
>> >
>> > Transactions are far safer and fast too.
>> >
>> > Indeed, transactions are safer.
>>
>> But I wonder, why did you experienced corruption with this pragma? Was
it
>> because of crashes of the OS or the application? Or are there other
>> factors
>> which can corrupt the data if not syncing ? As I understood from the
>> documentation, the only thing that can corrupt data when using this
>> pragma
>> are crashes and power failures.
>>
>>
>>
>> --
>> ...it's only a matter of time...
>>
>>
>
>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
...it's only a matter of time...

Re: Re[2]: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Ken <[EMAIL PROTECTED]> wrote:


Try without the pragma and wrap the inserts with a begin transaction and a
commit...

  The performance will be almost as good as with the pragma, with the
added benefit of consistent data and no corruption in the event of a crash
or power failure.



I know, but in my case, I can't use transactions due to the architecture of
the product.



DragonK <[EMAIL PROTECTED]> wrote:

  On 2/7/07, Teg wrote:
>
> Hello ohadp,
>
> Well, my experience is, particularly when it's users using it and not
> a personal project, that corruption happens fairly frequently when you
> use this pragma. That's why I don't use it any more in my production
> code.
>
> Transactions are far safer and fast too.
>
> Indeed, transactions are safer.

But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other
factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this pragma
are crashes and power failures.



--
...it's only a matter of time...





--
...it's only a matter of time...

Re: Re[2]: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Teg <[EMAIL PROTECTED]> wrote:


Hello ohadp,

Well, my experience is, particularly when it's users using it and not
a personal project, that corruption happens fairly frequently when you
use this pragma. That's why I don't use it any more in my production
code.

Transactions are far safer and fast too.

Indeed, transactions are safer.


But I wonder, why did you experienced corruption with this pragma? Was it
because of crashes of the OS or the application? Or are there other factors
which can corrupt the data if not syncing ? As I understood from the
documentation, the only thing that can corrupt data when using this pragma
are crashes and power failures.



--
...it's only a matter of time...

Re: [sqlite] Select and deletion

[DragonK]

On 2/7/07, anis chaaba <[EMAIL PROTECTED]> wrote:


with which language are you trying to do this?
to execute the delete statement you can use transactions to ensure
atomicity.
regards,



I'm using a C++ wrapper over sqlite.

The code i'm running is the following:

   SQLiteWrapper w;
   w.Open("test.db");
   w.DirectStatement("CREATE TABLE asd(ID INTEGER, A INTEGER, B
TEXT);");
   w.DirectStatement("PRAGMA synchronous = OFF;");

   char buf[1024];

   for (int i = 0 ; i < 1000; i++)
   {
   sprintf(buf,"INSERT INTO asd VALUES(%d, %d, 'asd')", i,
rand() % 6 );
   w.DirectStatement(buf);
   }

   // See how many rows with int == 2 we have
   SQLiteStatement* s = w.Statement("SELECT COUNT(*) FROM asd WHERE
A=2;");

   s->NextRow();
   int cnt = s->ValueInt(0);
   fprintf(stdout,"\nCount=%d", cnt);
   delete s;

   int cnt_real = 0;

   s = w.Statement("SELECT ID FROM asd WHERE A=2;");

   while (s->NextRow())
   {
   int id = s->ValueInt(0);
   char buff[256];
   sprintf(buff,"DELETE FROM asd WHERE ID=%d", id);

   if ( w.DirectStatement(buff) )
   {
   cnt_real++;
   }
   else
   {
   fprintf(stderr,"\nError...");
   }
   }

   delete s;

   fprintf(stdout,"\nDeleted count=%d\n", cnt_real);

   w.Close();

This seems to work... but I'm not sure if it should, that's why I'm asking.
I hope the code will show more exactly what I intend to do. Sorry for the
lack of proper error checking, it's just a test program. :) Also, I'm not
using any transactions, just the PRAGMA synchronous= off;




--
...it's only a matter of time...

Re: [sqlite] how can i speed up inserts ?

[DragonK]

On 2/7/07, Ohad Eder-Pressman <[EMAIL PROTECTED]> wrote:


i've got sqlite compiled with visual-studio, with all the default options,
didn't touch anything.
inserts are just a bit too slow for me, is there a good way to speed this
up
using some flags ?
any other flags that it would be smart to turn on for some better
performance ?




Try using transactions for an insert and turning off synchronization via
Pragma synchronize.

See here: http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html


thanks






--
...it's only a matter of time...

[sqlite] Select and deletion

[DragonK]

Hello everybody,

I'm wondering if the following scenario would work properly:

Run a SELECT query on the database and in a loop retrieve each result;
for each retrieved result, inside the same loop, execute a DELETE statement
for the previously extracted row.

I need this in order to retrieve a list of filenames from the database and
delete
each file from the hard disk and from the database also and I'm trying to
avoid
creating a temporary list for storing the file names.

Thanks in advance,
D.

--
...it's only a matter of time...