Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #38039 > unrolled thread

programming...?

Started byEric Douglas <e.d.programmer@gmail.com>
First post2018-02-20 07:38 -0800
Last post2018-02-22 19:32 -0500
Articles 7 on this page of 47 — 6 participants

Back to article view | Back to comp.lang.java.programmer

Contents

  programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 07:38 -0800
    Re: programming...? Jon <jon101011@yahoo.com> - 2018-02-20 17:07 +0100
      Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 08:19 -0800
        Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 11:45 -0500
          Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:39 -0800
            Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 13:25 -0800
              Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 14:00 -0800
              Re: programming...? Daniele Futtorovic <da.futt.news@laposte.net> - 2018-02-21 00:31 +0100
              Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 19:24 -0500
            Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 19:21 -0500
    Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-20 11:41 -0500
    Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 19:37 +0000
      Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:10 -0800
        Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 22:00 +0000
    Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-20 21:09 +0100
      Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-20 12:16 -0800
        Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-20 22:09 +0000
          Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-21 01:16 +0100
            Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 00:47 +0000
              Re: programming...? Robert Spanjaard <spamtrap@arumes.com> - 2018-02-21 12:29 +0100
                Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 10:42 -0500
                Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 15:44 +0000
                  Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:03 -0500
                    Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 08:38 -0800
                      Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:54 -0500
                        Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:40 -0800
                          Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 14:35 -0500
                            Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 11:48 -0800
                  Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 08:29 -0800
                    Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 11:47 -0500
                    Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 17:35 +0000
                      Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 12:47 -0500
                        Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:45 -0800
                          Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 14:40 -0500
                        Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 21:23 +0000
                          Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 21:39 -0500
                            Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-22 09:06 +0000
                              Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-24 20:29 -0500
                                Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-25 02:12 +0000
                                Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-26 08:19 -0800
                                  Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-03-01 13:17 -0500
                      Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-21 10:43 -0800
                        Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-21 21:14 +0000
                          Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-21 21:35 -0500
                            Re: programming...? Kiwi User <martin@mydomain.invalid> - 2018-02-22 09:10 +0000
                            Re: programming...? Eric Douglas <e.d.programmer@gmail.com> - 2018-02-22 06:23 -0800
                              Re: programming...? Arne Vajhøj <arne@vajhoej.dk> - 2018-02-22 19:32 -0500

Page 3 of 3 — ← Prev page 1 2 [3]

#38086

FromArne Vajhøj <arne@vajhoej.dk>
Date2018-03-01 13:17 -0500
Message-ID<p79g3l$1l7m$1@gioia.aioe.org>
In reply to#38084
On 2/26/2018 11:19 AM, Eric Douglas wrote:
> On Saturday, February 24, 2018 at 8:30:08 PM UTC-5, Arne Vajhøj wrote:
>> SQLServer Express can be managed via SQLServer Management Studio.
>>
>> SQLServer CE can no longer be managed via SQLServer Management Studio,
>> but supposedly you can do something from Vosual Studio and there are
>> third party GUI's availabel too.
>>
>> In both cases the DIY minded can use INFORMATION_SCHEMA views.
>>
>> If it is some other database, then who knows.
> 
> Good data modeling tool?  As I recall I couldn't find one for SQL Server.  The one built into MySQL seems decent.

Not sure about good, but there are options.

Some tools from non-modelling management tools to real modelling tools:
* SQLServer Management Studio
* Toad for SQLServer
* DBArtisan
* SQL Power Architect

(the last one is actually a Java application)

Arne

[toc] | [prev] | [next] | [standalone]

#38069

FromEric Douglas <e.d.programmer@gmail.com>
Date2018-02-21 10:43 -0800
Message-ID<0cf6b78f-a709-4325-93f0-3012aedff160@googlegroups.com>
In reply to#38066
On Wednesday, February 21, 2018 at 12:36:01 PM UTC-5, Kiwi User wrote:
> I think there's a general opinion that MariaDB, a fork of MySQL, is 
> better than its parent. I haven't used either, so don't know whether that 
> opinion is due to Oracle owning MySQL, Michael Widenius having baled 
> after Oracle bought Sun and started MariaDB, or for some other reason. 
> I've not used either. All I know is that RedHat Fedora switched from MySQL 
> to MariaDB as its default small DBMS.
> 
> At the time I needed to choose an RDBMS MySQL was still much better as a 
> searcheable data source than it was as a transactional DB and I prefer 
> the PostgreSQL sequence data type to MySQL's autoincrementing integer 
> fields, so I went with PostgreSQL on my Fedora systems and have never had 
> reason to regret that choice.
> 

Interesting, both open source, I'll have to check them out.  Usually I see businesses advertise for MS SQL Server or Oracle SQL, both paid software.
When I worked for Foot Locker of course the back end was Oracle SQL because the front end was PeopleSoft.

[toc] | [prev] | [next] | [standalone]

#38074

FromKiwi User <martin@mydomain.invalid>
Date2018-02-21 21:14 +0000
Message-ID<p6kneq$tce$1@news.albasani.net>
In reply to#38069
On Wed, 21 Feb 2018 10:43:41 -0800, Eric Douglas wrote:

> Interesting, both open source, I'll have to check them out.  Usually I
> see businesses advertise for MS SQL Server or Oracle SQL, both paid
> software.
>
My opinion about PostgreSQL sequences vs MySQL/MariaDB autoincrement is 
purely personal prejudice: 

- PostgreSQL: Each time you read a sequence you get the next
  sequential value.

- MySQL: Each time you store a row with an autoincrementing field that
         field is set to the next sequential value.

It's obvious that each sequence generates its own set of values because 
you can have many of them in a schema, but I don't know/can't remember if 
there is a separate autoincrementing source for each autoincrementing int
in the schema or whether they all feed off an common source. 

Both approaches work well for generating soft DB keys, which by 
definition must be unique within a table, if you're using them as (part 
of) a prime key. Reasons for doing that range from the need to store 
entities that have no guaranteed unique natural key to data compression, 
which can be a valid approach if the natural key is rather long, e.g. the 
name of a classical music work which can be up to 60 characters long.

My preference is for clarity: its obvious where the PK value comes from 
if there's (pseudocode) statements like this:

   newDBK = sequence.value
   insert (newDBK, musicTitle) in titleTable
   for all parts of work
       insert (newDBK, partno, movementTitle) in partWorkTable.

while to use an autoincrementing int you have to do this:

   insert (musicTitle in titleTable)
   newDBK = select max(dbk) from titleTable
   for all parts of work
       insert (newDBK, partno, movementTitle) in partWorkTable.
  
I think the first of these is a bit easier to understand, but YMMV. 


-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38076

FromArne Vajhøj <arne@vajhoej.dk>
Date2018-02-21 21:35 -0500
Message-ID<p6la8m$1ete$1@gioia.aioe.org>
In reply to#38074
On 2/21/2018 4:14 PM, Kiwi User wrote:
> On Wed, 21 Feb 2018 10:43:41 -0800, Eric Douglas wrote:
> 
>> Interesting, both open source, I'll have to check them out.  Usually I
>> see businesses advertise for MS SQL Server or Oracle SQL, both paid
>> software.
>>
> My opinion about PostgreSQL sequences vs MySQL/MariaDB autoincrement is
> purely personal prejudice:
> 
> - PostgreSQL: Each time you read a sequence you get the next
>    sequential value.
> 
> - MySQL: Each time you store a row with an autoincrementing field that
>           field is set to the next sequential value.
> 
> It's obvious that each sequence generates its own set of values because
> you can have many of them in a schema, but I don't know/can't remember if
> there is a separate autoincrementing source for each autoincrementing int
> in the schema or whether they all feed off an common source.

It is per field/table (since there can only be one such field
in a table  then per field and per table is the same).

> Both approaches work well for generating soft DB keys, which by
> definition must be unique within a table, if you're using them as (part
> of) a prime key. Reasons for doing that range from the need to store
> entities that have no guaranteed unique natural key to data compression,
> which can be a valid approach if the natural key is rather long, e.g. the
> name of a classical music work which can be up to 60 characters long.
> 
> My preference is for clarity: its obvious where the PK value comes from
> if there's (pseudocode) statements like this:
> 
>     newDBK = sequence.value
>     insert (newDBK, musicTitle) in titleTable
>     for all parts of work
>         insert (newDBK, partno, movementTitle) in partWorkTable.

Yes.

> while to use an autoincrementing int you have to do this:
> 
>     insert (musicTitle in titleTable)
>     newDBK = select max(dbk) from titleTable
>     for all parts of work
>         insert (newDBK, partno, movementTitle) in partWorkTable.

That is not how you do it. This is a very bad way of doing it.

At a low transaction isolation level this logic is not
concurrency safe.

And a high transaction isolation level impact performance.

The correct way is:

      insert (musicTitle in titleTable)
      newDBK = function_last_autogenerated_key_on_connection()
      for all parts of work
          insert (newDBK, partno, movementTitle) in partWorkTable.

MySQL has the function as LAST_INSERT_ID()

MS SQLServer has the function as SCOPE_IDENTiTY()

IBM DB2 has the function IDENTITY_VAL_LOCAL()

JDBC can do it database independent via Statement.getGeneratedKeys()

Arne

[toc] | [prev] | [next] | [standalone]

#38079

FromKiwi User <martin@mydomain.invalid>
Date2018-02-22 09:10 +0000
Message-ID<p6m1eb$nup$2@news.albasani.net>
In reply to#38076
On Wed, 21 Feb 2018 21:35:01 -0500, Arne Vajhøj wrote:

> The correct way is:
> 
>       insert (musicTitle in titleTable)
>       newDBK = function_last_autogenerated_key_on_connection()
>       for all parts of work
>           insert (newDBK, partno, movementTitle) in partWorkTable.
> 
> MySQL has the function as LAST_INSERT_ID()
> 
> MS SQLServer has the function as SCOPE_IDENTiTY()
> 
> IBM DB2 has the function IDENTITY_VAL_LOCAL()
> 
> JDBC can do it database independent via Statement.getGeneratedKeys()
>
Thanks for that. And, of course, the same will work with MariaDB.
 

-- 
Martin    | martin at
Gregorie  | gregorie dot org

[toc] | [prev] | [next] | [standalone]

#38080

FromEric Douglas <e.d.programmer@gmail.com>
Date2018-02-22 06:23 -0800
Message-ID<d3d8fe28-7e39-446c-ace0-28955ba13e5e@googlegroups.com>
In reply to#38076
On Wednesday, February 21, 2018 at 9:35:16 PM UTC-5, Arne Vajhøj wrote:
> The correct way is:
> 
>       insert (musicTitle in titleTable)
>       newDBK = function_last_autogenerated_key_on_connection()
>       for all parts of work
>           insert (newDBK, partno, movementTitle) in partWorkTable.
> 
> MySQL has the function as LAST_INSERT_ID()
> 
> MS SQLServer has the function as SCOPE_IDENTiTY()
> 
> IBM DB2 has the function IDENTITY_VAL_LOCAL()
> 
> JDBC can do it database independent via Statement.getGeneratedKeys()
> 
> Arne

Getting a last ID written to increment for a new key doesn't work unless there's either a record locking method so no other process is getting the last ID until after you've written the new record, or an error check that knows you're trying to write a new record, fails if it already exists, and goes back to getting the last ID again.

In our old sequencing process we have one record contain the next sequence ID.  Then any process trying to write a new record has to lock that record (other processes can read it but cannot lock it), then write their new record, then write out the incremented sequence which releases the lock.  Of course this requires a method to release locks if a process crashes after locking before they get to releasing.  It seems like a database with an auto-sequence field that somehow recognizes the writing of new records and somehow makes the key unique for you would be easier, though it should probably return a value in case you need to care what that sequence is.

[toc] | [prev] | [next] | [standalone]

#38081

FromArne Vajhøj <arne@vajhoej.dk>
Date2018-02-22 19:32 -0500
Message-ID<p6nne8$1f01$1@gioia.aioe.org>
In reply to#38080
On 2/22/2018 9:23 AM, Eric Douglas wrote:
> On Wednesday, February 21, 2018 at 9:35:16 PM UTC-5, Arne Vajhøj wrote:
>> The correct way is:
>>
>>        insert (musicTitle in titleTable)
>>        newDBK = function_last_autogenerated_key_on_connection()
>>        for all parts of work
>>            insert (newDBK, partno, movementTitle) in partWorkTable.
>>
>> MySQL has the function as LAST_INSERT_ID()
>>
>> MS SQLServer has the function as SCOPE_IDENTiTY()
>>
>> IBM DB2 has the function IDENTITY_VAL_LOCAL()
>>
>> JDBC can do it database independent via Statement.getGeneratedKeys()
> 
> Getting a last ID written to increment for a new key doesn't work
> unless there's either a record locking method so no other process is
> getting the last ID until after you've written the new record, or an
> error check that knows you're trying to write a new record, fails if
> it already exists, and goes back to getting the last ID again.
No locking required - these functions work per connection.

Arne

[toc] | [prev] | [standalone]

Page 3 of 3 — ← Prev page 1 2 [3]

Back to top | Article view | comp.lang.java.programmer

csiph-web