[sqlite] Database modeling question

[Alem Biscan]

Hello Comunity,

I have a question that is more of database modeling theory ( normalisation
/ data organisation ) nature. Please advise me.

For a job interview I am required among other things to create a phone book
application. I am thinking about database design. Although it is a simple
assigment, I would like to make good first impression and do It by the
book. Show them I understand relational database concepts. The application
should list phone book entries. Also it should allow insertion, deletion,
and modification.

Name   Surname   City Phonenumber1 .. PhonenumberN
=
Mark KnolpferLos Angeles 0002224343.. 4443332233


..

Allthough a denormalised model of 1, maybe 2 tables would work fine in
reality, I want to go by the book. I want your reviews and possible
improvements.

 I was thinking of having 4 tables. PERSON, PHONE_NUMBER, PERSON_CITY ,
CITY.

It seems somehow stupid to have a table that holds only the phone number.
And relation of person and phone number is 1 .. N.

Please suggest. What would be the right way considering normalisation by
the book.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Instead of triggers

[Alem Biscan]

Hello community,

I am a little bit confused by instead of update trigger logic.

Lets say there is a trigger MYTRIGGER which is an INSTEAD OF UPDATE trigger
that belongs to view named MYVIEW.
MYTRIGGER should update table MYTABLE with values sent from an application.

 UPDATE MYTABLE
   SET *primary = new . primary; *
 * field2= new . field2*,
  *field3= new . field3*
 WHERE *primary = old.primary;*

So user decides to modify a record. He changes *primary, field2,
field3*values trough textboxes, and the app sends parametarised update
query to
sqlite engine.

UPDATE MYVIEW
 SET *primary = @a*,
*field2= @b*.
*field3= @c*;

Since *primary* is not a surrogat key, and the user has changed it, there
is no way to and including it in a where clause.

My question is: does sqlite engine know the *old value of primary field*?
If yes, HOW? Is trigger executing once or for each row?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trigger blocks a single transaction?

[Alem Biscan]

Hello,

I am wandering if i have a block of dml statements in a trigger ( instead
of ). Is it a single transaction? If something fails everything fails or?

SELECT
CASE
WHEN
NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
. tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
tk . tkla_naziv = 'GRUPA' )
THEN
RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
WHEN
NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE kl
. tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
tk . tkla_naziv = 'TIP ARTIKLA' )
THEN
RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
END;

UPDATE katklas kkl
   SET kkl.kkl_kat_sifra  = new.kat_sifra
  ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
 WHERE kkl.kkl_kat_sifra  = old.kat_sifra
   AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;

UPDATE katklas kkl
SET kkl.kkl_kat_sifra  = new.kat_sifra
  ,kkl.kkl_klas_sifra = new.grupa_klas_sifra
WHERE kkl.kkl_kat_sifra  = old.kat_sifra
AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;

UPDATE katalog kat
   SET kat.kat_sifra   = new.kat_sifra
  ,kat.kat_barcode = new.kat_barcode
  ,kat.kat_naziv   = new.kat_naziv
  ,kat.kat_jmj = new.kat_jmj
  ,kat.kat_car = new.kat_car
  ,kat.kat_mpc = new.kat_mpc
  ,kat.kat_porez   = new.kat_porez
WHERE kat.kat_sifra = old.kat_sifra;

If first update fails, is it considered a failiure for the whole thing?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger blocks a single transaction?

[Alem Biscan]

Hi,

No, i do not execute begin/commit. It is VIEW'S INSTEAD OF UPDATE TRIGGER.
I am doing a regular update to the view from C#. Another thing is that view
doesn't return any row affected value. Well it makes sense somehow.. It
cannot know how many view's visible rows were affected. It lowers the
coolnes of views and instead of trigs.

Thanx

On Mon, Dec 24, 2012 at 12:15 AM, Pavel Ivanov paiva...@gmail.com wrote:

 Do you execute all updates as one call to sqlite_exec? Or as separate
 calls to sqlite_exec or sqlite_prepare/sqlite_step? If separate then
 do you check return value from the calls? And do you execute
 BEGIN/COMMIT somewhere?

 Pavel

 On Sun, Dec 23, 2012 at 2:37 PM, Alem Biscan biscana...@gmail.com wrote:
  Hello,
 
  I am wandering if i have a block of dml statements in a trigger ( instead
  of ). Is it a single transaction? If something fails everything fails or?
 
  SELECT
  CASE
  WHEN
  NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
 kl
  . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
  tk . tkla_naziv = 'GRUPA' )
  THEN
  RAISE ( ROLLBACK , ' * Ne postoji grupa pod šifrom ' )
  WHEN
  NOT EXISTS ( SELECT klas_sifra FROM klasifikacija kl , tipklas tk WHERE
 kl
  . tkla_id = tk . tkla_id AND kl . klas_sifra = new . grupa_klas_sifra AND
  tk . tkla_naziv = 'TIP ARTIKLA' )
  THEN
  RAISE ( ROLLBACK , ' * Ne postoji tip artikla pod šifrom ' )
  END;
 
  UPDATE katklas kkl
 SET kkl.kkl_kat_sifra  = new.kat_sifra
,kkl.kkl_klas_sifra = new.grupa_klas_sifra
   WHERE kkl.kkl_kat_sifra  = old.kat_sifra
 AND kkl.kkl_klas_sifra =  old.grupa_klas_sifra;
 
  UPDATE katklas kkl
  SET kkl.kkl_kat_sifra  = new.kat_sifra
,kkl.kkl_klas_sifra = new.grupa_klas_sifra
  WHERE kkl.kkl_kat_sifra  = old.kat_sifra
  AND kkl.kkl_klas_sifra =  old.tip_klas_sifra;
 
  UPDATE katalog kat
 SET kat.kat_sifra   = new.kat_sifra
,kat.kat_barcode = new.kat_barcode
,kat.kat_naziv   = new.kat_naziv
,kat.kat_jmj = new.kat_jmj
,kat.kat_car = new.kat_car
,kat.kat_mpc = new.kat_mpc
,kat.kat_porez   = new.kat_porez
  WHERE kat.kat_sifra = old.kat_sifra;
 
  If first update fails, is it considered a failiure for the whole thing?
  ___
  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

[sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[Alem Biscan]

Hello,

I am developing an application using Visual Studio 2008 ( C# .NET 3.5SP1 )
and sqlite ( System.Data.Sqlite ).
Whole database interaction is written using strongly typed datasets. All
was fine while working with tables, but when i tried to
update views, it was brick wall. I was hoping to use sqlite view just like
any table trough the designer. Issue commands, and let instead of trigger
to do the logic.

 try
 {
 this.katalogvBindingSource.RemoveCurrent();
 }
 catch (Exception exception)
 {
 }

try
{
this.katalogvTableAdapter.Update(fkasaDataSet.katalogv);
}
catch (SqliteException ex)
{
}

*Throws DBconcurrency violation. Affected rows 0 instead of 1.*

Even though i have turned optimistic concurrency off.

Delete command works fine. Executed the command trough VS dataset designer,
and everything was fine, instead of trigger did all buisness logic it was
designed for. Since i ' ve had the same problem testing on postgreSQL, i am
thinking this is probably typed dataset bug, and not sqlite problem. Since
no one replied to my question on official .NET forums, i am desperatly
trying here. Maybe someone helps. If not , oh well i will manualy code it.
But would like to keep coding style consistency.

Maybe someone knows how to bypass this.

Thank you in advance,
Alem Bišćan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

[Alem Biscan]

Thank you guys very much for answering,

Igor, i belive you are right. I think it is a typed dataset .NET bug. There
exists an option to use optimistic concurency. If it's on then it uses rows
affected to check if row was modified or not. The stupidity of it is even
if it is turned off ( checkbox unchecked ), it still throws the exception.
I am hoping that someone else working with System.Data.Sqlite and Typed
dataSets  encountered the same problem, and perhaps knows the solution.
Well, at least i have reduced the problem to typed DS, and eliminated
SQLite connector.

Alem

On Fri, Dec 21, 2012 at 1:48 AM, Igor Tandetnik i...@tandetnik.org wrote:

 On 12/20/2012 7:21 PM, BareFeetWare wrote:

 The changes are in fact made, but those avenues for checking don't work.
 I'm tempted to label this as a bug in SQLite, since I see no reason for the
 limitation.


 If the trigger changes 20 records across 5 tables, which results in 63
 rows in the view visibly affected, which value should sqlite3_changes
 return?
 --
 Igor Tandetnik


 __**_
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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