[sqlite] Question about database design

[Bert Nelsen]

Hello!

I have over 100 columns in a table. Most of the columns will stay empty.
Just an example:

customer_lastname
customer_firstname
customer_street
customer_PhonePrivate (will almost always stay empty)
customer_PhoneCompany
customer_PhoneMobile
customer_PhoneWAP (will almost always stay empty)
customer_Phone1 (will almost always stay empty)
customer_Phone2 (will almost always stay empty)

Because I felt so stupid about these mostly empty columns taking so much
space, I tended to replace all the "phone" columns by a single column named
"customerPhone".
I stored the values into customerPhone like that:

[sqlite] Storing large numeric values

[Bert Nelsen]

I am trying to save values like 19.000.000.000 to my database but I haven't
found the appropriate column type yet. Can anybody help please.
I am using the dhRichClient command object, but even Int64 isn't large
enough.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

Thanks Olaf.

On Sat, Jan 2, 2010 at 12:49 AM, Olaf Schmidt  wrote:

>
> "Artur Reilin"  schrieb im
> Newsbeitrag news:op.u5vno6hp1pq...@rear...
>
> > If you are using something like an log system it
> > would be better in this way, but in apps like an
> > shop what wouldn't be so great. (thinking about
> > ebay with the bets and such..)
>
> Of course, but I think I made that already clear,
> that the approach should not to be misunderstood as a
> "general recommendation" - it really should be used only within
> smaller Apps, which don't need e.g. "stacked transactions",
> or "complex transactions which could fail" ... Apps which
> also only work singlethreaded within a single process ...
> ...the timer-based transaction-syncing then only an
> "easier applicable workaround" in environments which
> cannot - (or don't want to) make use of the more efficient
> working async-writer-thread implementation of the SQLite-engine).
>
> Olaf Schmidt
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
Bert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

So SQLite looks at both the database on the disk and in memory?
Wouldn't that be difficult???
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

Hello Olaf!
Thanks again for the message.
By "corrupted" (the meaning of this word seems to differ depending on the
user's perspective) you mean that the db is really destroyed and cannot be
opened anymore?
Currently I am working on an application that tracks something like a GPS
signal, and if a signal is not saved to the db it's not a big problem. But
db corruption would be.
Another question would be: When I use transactions, and I said ".BeginTrans"
and insert new records and then, before saying ".CommitTrans", I query the
records, they seem to be already saved. Can you tell me why this is so? Does
a select command automatically trigger a ".CommitTrans"?
Wishing you a happy new year!

On Fri, Jan 1, 2010 at 9:11 AM, Olaf Schmidt <s...@online.de> wrote:

>
> "Bert Nelsen" <bert.nel...@googlemail.com> schrieb
> im Newsbeitrag
> news:a5ffd530912311004p26a7cc5k1f1bf6f671bef...@mail.gmail.com...
>
> > Your .Sychronous = False property does everything
> > as fast as I want, and I am not afraid of losing some
> > user data (it's not a critical application) but
> > I am very much afraid of having a corrupted db.
> > Can anybody please confirm
> > that there is no chance of getting my db corrupted?
>
> Ah, I see now, where the "confusion" came from.
> The wrappers Synchronous-Property has nothing to do
> with the (relative new) async-writer-feature of SQLite -
> instead it maps to SQLites Synchronous PRAGMA
> (as a "convenience property").
>
> You can set all the Pragmas alternatively also per
> Cnn.Execute "PRAGMA pragma_name ..."
>
> or read out a current Pragma-Value with:
> Cnn.OpenRecordset("PRAGMA pragma_name")(0).Value
>
> Please read about SQLite-Pragmas here:
> http://www.sqlite.org/pragma.html
> ... and what's written there about the Synchronous-Pragma-
> Settings. With the Synchronous-Pragma at 'Off' or '0', you're
> risking DB-corruption.
>
> So, I would not touch the Synchronous-Property in your
> case (leaving it at its default FULL(2)) - instead you should
> wrap larger insert- or update-actions within a transaction -
> that works fast as well.
> Also consider using the binding-support of the wrapper
> (the Command-Objects), to achieve faster (and more
> typesafe) operations in "write direction".
>
> Olaf Schmidt
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
Bert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

Hello Olaf!
Thank you for your reply. I am using your wrapperlib (and I love it) but I
thought it was a more general question and had nothing directly to do with
your wrapperlib. But I am not sure anymore what is yours and what is common
Sqlite.
Your .Sychronous = False property does everything as fast as I want, and I
am not afraid of losing some user data (it's not a critical application) but
I am very much afraid of having a corrupted db. Can anybody please confirm
that there is no chance of getting my db corrupted?

On Thu, Dec 31, 2009 at 6:11 PM, Olaf Schmidt <s...@online.de> wrote:

>
> "Bert Nelsen" <bert.nel...@googlemail.com> schrieb
> im Newsbeitrag
> news:a5ffd530912310853t7024d908tefbf1ef40df47...@mail.gmail.com...
>
> > I would like to make writes to my SQLite db faster.
> In what regard (under which circumstances) is it slow
> currently?
>
> What's your typical recordcount per transaction?
> Do you use transactions at all?
> How many indexes are defined on the "slow tables"?
> Are you using Command-Objects (the sqlite-bind API)?
> In what language is the App-Host written (regarding
> easy "thread-control")?
> Do you work through a wrapper yet, or with the SQLite-lib
> directly?
>
> > I was thinking about the Async method, but I think I
> > remember reading somewhere that it may cause database
> > corruption.
> IMO the async-feature was not risky with regards to data-
> corruption, only with regards to durability - an (uncorrupted)
> DB could contain "lesser data" (not the last "version", which
> your successfully reported transactions suggested earlier) -
> in case of e.g. a powerfailure.
> But your "mozilla-comment-snippet" already mentioned that too.
>
> I remember, that at some point in time you were using my
> COM-wrapper, to work with SQLite. And I was thinking
> about "handing out" the async-functionality over an appropriate
> method, as the feature came up - but then stayed away from
> it, for the sake of higher stability. The COM-wrapper is
> mostly used in conjunction with VB5/6 - and there you have
> not that much control over the teardown-process of an App
> like in other, more "bare to the metal" languages - imagine
> the async SQLite-WriterThread, having "yet some stuff
> in the pipe" - and a closing VB-App, which only "knows"
> the COM-Object - and not the SQLite-lib behind it, which
> "owns" the async thread.
>
> So, just in case you're using the dhRichClient3-wrapper-lib,
> there's currently no plan (and no time), to make that feature
> available in a reliable and stable working way (playing well
> with VB5/6) over the COM-interface.
>
> Olaf Schmidt
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Regards,
Bert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mozilla's method

[Bert Nelsen]

It's here:

https://developer.mozilla.org/en/Storage:Performance

On Thu, Dec 31, 2009 at 6:04 PM, Shawn Wilsher <comrade...@gmail.com> wrote:

> Where did you see this?  It's inaccurate, and if I can, I'll remove it.  If
> you want to use a proper asynchronous statement execution which wraps your
> statement[s] in a transaction, you want to use this:
> https://developer.mozilla.org/En/Storage#Asynchronously
>
> Cheers,
>
> Shawn Wilsher
> Mozilla Developer
>
> On Thu, Dec 31, 2009 at 8:53 AM, Bert Nelsen <bert.nel...@googlemail.com
> >wrote:
>
> > Hello,
> >
> > I would like to make writes to my SQLite db faster.
> >
> > I was thinking about the Async method, but I think I remember reading
> > somewhere that it may cause database corruption.
> >
> > Now I read something on the Mozilla pages, and I don't understand what
> > exactely they are doing.
> >
> > Do they bundle everything in transactions only or do they in fact use the
> > Async method?
> > It is not clear to me by reading through their articles.
> >
> > It would be nice if somebody could clear me up on this issue.
> >
> > Here is the article:
> >
> > Lazy writing
> >
> > Mozilla has relaxed the ACID requirements in order to speed up commits.
> In
> > particular, we have dropped durability. This means that when a commit
> > returns, you are not guaranteed that the commit has gone through. If the
> > power goes out right away, that commit may (or may not) be lost. However,
> > we
> > still support the other (ACI) requirements. This means that the database
> > will not get corrupted. If the power goes out immediately after a commit,
> > the transaction will be like it was rolled back: the database will still
> be
> > in a consistent state.
> >
> > Higher commit performance is achieved by writing to the database from a
> > separate thread (see
> > storage/src/mozStorageAsyncIO.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageAsyncIO.cpp
> > >
> > which
> > is associated with the storage service in
> > storage/src/mozStorageService.cpp<
> >
> http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageService.cpp
> > >
> > ).
> > The main database thread does everything exactly as it did before.
> However,
> > we have overridden the file operations and everything comes through the
> > AsnycIO module. This file is based on
> > test_async.c<
> http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/test_async.c
> > >from
> > the sqlite distribution.
> >
> > The AsyncIO module packages writes up in messages and puts them on the
> > write
> > thread's message queue. This write thread waits for messages and
> processes
> > them as fast as it can. This means that writes, locking, and most
> > importantly, disk syncs, only block the AsyncIO thread. Reads are done
> > synchronously, taking into account unwritten data still in the buffer.
> > ___
> > 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
>



-- 
Regards,
Bert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Mozilla's method

[Bert Nelsen]

Hello,

I would like to make writes to my SQLite db faster.

I was thinking about the Async method, but I think I remember reading
somewhere that it may cause database corruption.

Now I read something on the Mozilla pages, and I don't understand what
exactely they are doing.

Do they bundle everything in transactions only or do they in fact use the
Async method?
It is not clear to me by reading through their articles.

It would be nice if somebody could clear me up on this issue.

Here is the article:

Lazy writing

Mozilla has relaxed the ACID requirements in order to speed up commits. In
particular, we have dropped durability. This means that when a commit
returns, you are not guaranteed that the commit has gone through. If the
power goes out right away, that commit may (or may not) be lost. However, we
still support the other (ACI) requirements. This means that the database
will not get corrupted. If the power goes out immediately after a commit,
the transaction will be like it was rolled back: the database will still be
in a consistent state.

Higher commit performance is achieved by writing to the database from a
separate thread (see
storage/src/mozStorageAsyncIO.cpp
which
is associated with the storage service in
storage/src/mozStorageService.cpp
).
The main database thread does everything exactly as it did before. However,
we have overridden the file operations and everything comes through the
AsnycIO module. This file is based on
test_async.cfrom
the sqlite distribution.

The AsyncIO module packages writes up in messages and puts them on the write
thread's message queue. This write thread waits for messages and processes
them as fast as it can. This means that writes, locking, and most
importantly, disk syncs, only block the AsyncIO thread. Reads are done
synchronously, taking into account unwritten data still in the buffer.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index is not being used

[Bert Nelsen]

Hello!

I have the problem that one index is not taken into account when I execute a
query:

I have 1 table with 2 columns.
Each column has an index, but EXPLAIN QUERY PLAN tells me that the second
index is not being used.

--
First column:
name: mycolumn1
type: text
collation: nocase
unique: yes

First column index:
name: idx_mycolumn1
collation: nocase
unique: yes
---
Second column:
name: mycolumn2
type: text
collation: -
unique: false

Second column index:
name: idx_mycolumn2
collation: -
unique: false
--

Results for "SELECT * FROM mytable WHERE mycolumn1 LIKE 'a%'":
1: 0
2: TABLE mytable WITH INDEX idx_mycolumn1

Results for "SELECT * FROM mytable WHERE mycolumn2 LIKE 'a%'"
0
TABLE mytable

Can anybody tell me why and how I can change this?

Thank you.

-- 
Regards,
Bert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users