Can I get help with db design for SQLite use?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Can I get help with db design for SQLite use?

Tom Browder
I need help with a db design to be modeled for use with SQLite. The design
uses foreign keys and I am just now using the foreign pragmas and other
parts of SQLite to help me make it all work together.

Is this list appropriate for presenting my design and requesting advice?

Best regards,

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Simon Slavin-3
On 2 Apr 2019, at 3:48pm, Tom Browder <[hidden email]> wrote:

> I need help with a db design to be modeled for use with SQLite. The design uses foreign keys and I am just now using the foreign pragmas and other parts of SQLite to help me make it all work together.
>
> Is this list appropriate for presenting my design and requesting advice?

Yes, you can post a schema here (paste it into your message, attachments are stripped) and ask for advice on whether you have the right tables, columns, indexes and keys.  Be prepared to get the advice that you're doing everything wrong and should rethink it all from scratch.

Do be aware that almost all of us are just users like you.  We are not professional designers, we argue with one-another, and you can't sue us if we give you bad advice.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Don V Nielsen
>  Do be aware that almost all of us are just users like you.

And be aware these guys are freakin brilliant. No lie.

On Tue, Apr 2, 2019 at 10:26 AM Simon Slavin <[hidden email]> wrote:

> On 2 Apr 2019, at 3:48pm, Tom Browder <[hidden email]> wrote:
>
> > I need help with a db design to be modeled for use with SQLite. The
> design uses foreign keys and I am just now using the foreign pragmas and
> other parts of SQLite to help me make it all work together.
> >
> > Is this list appropriate for presenting my design and requesting advice?
>
> Yes, you can post a schema here (paste it into your message, attachments
> are stripped) and ask for advice on whether you have the right tables,
> columns, indexes and keys.  Be prepared to get the advice that you're doing
> everything wrong and should rethink it all from scratch.
>
> Do be aware that almost all of us are just users like you.  We are not
> professional designers, we argue with one-another, and you can't sue us if
> we give you bad advice.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
In reply to this post by Simon Slavin-3
On Tue, Apr 2, 2019 at 10:26 Simon Slavin <[hidden email]> wrote:
> On 2 Apr 2019, at 3:48pm, Tom Browder <[hidden email]> wrote:
> > I need help with a db design to be modeled for use with SQLite. The design uses foreign keys and I am just now using the foreign pragmas and other parts of SQLite to help me make it all work together.
> >
> > Is this list appropriate for presenting my design and requesting advice?
...

> Yes, you can post a schema here (paste it into your message, attachments are stripped)
...
> Do be aware that almost all of us are just users like you.  We are not professional
> designers, we argue with one-another, and you can't sue us if we give you bad advice.

Thanks, Simon, I will try to be a courteous and objective participant!

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
In reply to this post by Don V Nielsen
On Tue, Apr 2, 2019 at 10:40 AM Don V Nielsen <[hidden email]> wrote:
>
> >  Do be aware that almost all of us are just users like you.
>
> And be aware these guys are freakin brilliant. No lie.

I don't doubt it at all, but thanks for the warning, Don!

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

ams64@juno.com
In reply to this post by Tom Browder
You might want to import everything into SQLite Studio or SQLite Suite I forget the exact name)--a freebie on the internet. I found it worked for me. In order to help others, I would appreciate it if you tell us on this usergroup how you made out. May it work for you. Peace! Alex

---------- Original Message ----------
From: Tom Browder <[hidden email]>
To: SQLite mailing list <[hidden email]>
Subject: Re: [sqlite] Can I get help with db design for SQLite use?
Date: Tue, 2 Apr 2019 15:07:58 -0500

On Tue, Apr 2, 2019 at 10:26 Simon Slavin <[hidden email]> wrote:
> On 2 Apr 2019, at 3:48pm, Tom Browder <[hidden email]> wrote:
> > I need help with a db design to be modeled for use with SQLite. The design uses foreign keys and I am just now using the foreign pragmas and other parts of SQLite to help me make it all work together.
> >
> > Is this list appropriate for presenting my design and requesting advice?
...

> Yes, you can post a schema here (paste it into your message, attachments are stripped)
...
> Do be aware that almost all of us are just users like you.  We are not professional
> designers, we argue with one-another, and you can't sue us if we give you bad advice.

Thanks, Simon, I will try to be a courteous and objective participant!

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
____________________________________________________________
Our Hearts Go Out To Denzel Washington
go.dedicatedoffers.com
http://thirdpartyoffers.juno.com/TGL3131/5ca3e2679432962674b79st04vuc
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
On Tue, Apr 2, 2019 at 17:30 [hidden email] <[hidden email]> wrote:

> You might want to import everything into SQLite Studio or SQLite Suite I
> forget the exact name)--a freebie on the internet. I found it worked for
> me. In order to help others, I would appreciate it if you tell us on this
> usergroup how you made out. May it work for you. Peace! Alex


Thanks for the idea, Alex.

I'm doing all programmatically at the moment (using a Perl 6 module:
DB::SQLite), but using one of those tools you mentioned would help in
design for sure!

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
On Tue, Apr 2, 2019 at 18:21 Tom Browder <[hidden email]> wrote:

> On Tue, Apr 2, 2019 at 17:30 [hidden email] <[hidden email]> wrote:
>
>> You might want to import everything into SQLite Studio
>
>
It's SQLite Studio, and i used it a bit many years ago, but it has had a
recent update so I will give it a try--and report results.

And I also have the commercial Razor SQL to use.

Thanks again.

-Tom


-To

> or SQLite Suite I forget the exact name)--a freebie on the internet. I
>> found it worked for me. In order to help others, I would appreciate it if
>> you tell us on this usergroup how you made out. May it work for you. Peace!
>> Alex
>
>
> Thanks for the idea, Alex.
>
> I'm doing all programmatically at the moment (using a Perl 6 module:
> DB::SQLite), but using one of those tools you mentioned would help in
> design for sure!
>
> -Tom
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Rob Willett
In reply to this post by Tom Browder
Tom,

We use the Perl DB::SQLite module. It works very well and I cannot
recall a single issue with it in the last four years. There's not as
much support for Perl on this mailing list as it's not as popular, but
most issues you will probably encounter will be at the design level
rather than at the CPAN module level. Our working assumption is that if
the DBI module looks like it has an issue, it doesn't and it's our code.
Pretty much the same as for SQLite. Whilst I know that SQLite has had
bugs, the chances of us finding them is minimal, so we assume it's our
code again.

However I can say that we use Perl for all our system code and use
SQLite within it and its fast and easy.

If you're using Perl, I would also recommend the following other
modules, this is a direct pull from our code. I've removed our code
specific modules.

```
#!/usr/bin/perl -w

use strict;
use warnings;

use Switch;
use DBI;
use JSON;
use Getopt::Long;      <-- Easiest way to get command line args in and
processed.
use Data::Dumper;      <-- Utterly essential, don't leave home without
it.
use Mojolicious::Lite; <-- Only need if you making a REST based server
use Mojo::Parameters;  <-- Only need if you making a REST based server
use Mojo::URL;         <-- Only need if you making a REST based server
use Mojo::Log;         <-- Only need if you making a REST based server
use REST::Client; <-- More rest based stuff
use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
scalar_looks_like_sereal);
use DateTime;          <-- Manage date and time properly.
use Net::Curl::Easy;
use Crypt::Random qw( makerandom );
use Log::Log4perl qw(get_logger :levels);
use File::Path qw(make_path); <-- Quick and easy way to make paths and
directories.
use Net::Address::IP::Local;  <-- Easy way to manipulate IP addresses.
use File::Slurp; <-- Quick and easy way to read and write files.
use Clone 'clone'; <-- You'll use a lot of objects and structures. Copy
them properly and fast.

```

We use Log4Perl a lot as it's easy to get things setup and then you can
modify one log file and get easy changes. Also we use Mojolicious for
all the REST code wrapping.

These are tried and tested CPAN modules that we know just work for us
and are a standard part of every new build. The most awkward one if the
curl one, there seems to be a lot of versions of the Curl::Easy stuff
which simply don't work on Mac OS X (or other OS's).

Just my 2p worth.

Rob

On 3 Apr 2019, at 0:21, Tom Browder wrote:

> On Tue, Apr 2, 2019 at 17:30 [hidden email] <[hidden email]> wrote:
>
>> You might want to import everything into SQLite Studio or SQLite
>> Suite I
>> forget the exact name)--a freebie on the internet. I found it worked
>> for
>> me. In order to help others, I would appreciate it if you tell us on
>> this
>> usergroup how you made out. May it work for you. Peace! Alex
>
>
> Thanks for the idea, Alex.
>
> I'm doing all programmatically at the moment (using a Perl 6 module:
> DB::SQLite), but using one of those tools you mentioned would help in
> design for sure!
>
> -Tom
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Shawn Wagner
Yay Perl! My favorite language. DBD::SQLite is definitely one of the
better, fuller featured sqlite bindings out there. Though Tom is using
perl6, not perl5. I have no idea how its version compares.

(At the risk of going off topic, File::Slurp has issues. File::Slurper is a
better alternative.)

On Wed, Apr 3, 2019, 12:08 AM Rob Willett <[hidden email]>
wrote:

> Tom,
>
> We use the Perl DB::SQLite module. It works very well and I cannot
> recall a single issue with it in the last four years. There's not as
> much support for Perl on this mailing list as it's not as popular, but
> most issues you will probably encounter will be at the design level
> rather than at the CPAN module level. Our working assumption is that if
> the DBI module looks like it has an issue, it doesn't and it's our code.
> Pretty much the same as for SQLite. Whilst I know that SQLite has had
> bugs, the chances of us finding them is minimal, so we assume it's our
> code again.
>
> However I can say that we use Perl for all our system code and use
> SQLite within it and its fast and easy.
>
> If you're using Perl, I would also recommend the following other
> modules, this is a direct pull from our code. I've removed our code
> specific modules.
>
> ```
> #!/usr/bin/perl -w
>
> use strict;
> use warnings;
>
> use Switch;
> use DBI;
> use JSON;
> use Getopt::Long;      <-- Easiest way to get command line args in and
> processed.
> use Data::Dumper;      <-- Utterly essential, don't leave home without
> it.
> use Mojolicious::Lite; <-- Only need if you making a REST based server
> use Mojo::Parameters;  <-- Only need if you making a REST based server
> use Mojo::URL;         <-- Only need if you making a REST based server
> use Mojo::Log;         <-- Only need if you making a REST based server
> use REST::Client; <-- More rest based stuff
> use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
> use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
> scalar_looks_like_sereal);
> use DateTime;          <-- Manage date and time properly.
> use Net::Curl::Easy;
> use Crypt::Random qw( makerandom );
> use Log::Log4perl qw(get_logger :levels);
> use File::Path qw(make_path); <-- Quick and easy way to make paths and
> directories.
> use Net::Address::IP::Local;  <-- Easy way to manipulate IP addresses.
> use File::Slurp; <-- Quick and easy way to read and write files.
> use Clone 'clone'; <-- You'll use a lot of objects and structures. Copy
> them properly and fast.
>
> ```
>
> We use Log4Perl a lot as it's easy to get things setup and then you can
> modify one log file and get easy changes. Also we use Mojolicious for
> all the REST code wrapping.
>
> These are tried and tested CPAN modules that we know just work for us
> and are a standard part of every new build. The most awkward one if the
> curl one, there seems to be a lot of versions of the Curl::Easy stuff
> which simply don't work on Mac OS X (or other OS's).
>
> Just my 2p worth.
>
> Rob
>
> On 3 Apr 2019, at 0:21, Tom Browder wrote:
>
> > On Tue, Apr 2, 2019 at 17:30 [hidden email] <[hidden email]> wrote:
> >
> >> You might want to import everything into SQLite Studio or SQLite
> >> Suite I
> >> forget the exact name)--a freebie on the internet. I found it worked
> >> for
> >> me. In order to help others, I would appreciate it if you tell us on
> >> this
> >> usergroup how you made out. May it work for you. Peace! Alex
> >
> >
> > Thanks for the idea, Alex.
> >
> > I'm doing all programmatically at the moment (using a Perl 6 module:
> > DB::SQLite), but using one of those tools you mentioned would help in
> > design for sure!
> >
> > -Tom
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Rob Willett
Shawn,

I will investigate File::Slurper. Rather than pollute this list, send me
a mail on
[hidden email] about the issues you had.

Thanks

Rob

On 3 Apr 2019, at 8:50, Shawn Wagner wrote:

> Yay Perl! My favorite language. DBD::SQLite is definitely one of the
> better, fuller featured sqlite bindings out there. Though Tom is using
> perl6, not perl5. I have no idea how its version compares.
>
> (At the risk of going off topic, File::Slurp has issues. File::Slurper
> is a
> better alternative.)
>
> On Wed, Apr 3, 2019, 12:08 AM Rob Willett
> <[hidden email]>
> wrote:
>
>> Tom,
>>
>> We use the Perl DB::SQLite module. It works very well and I cannot
>> recall a single issue with it in the last four years. There's not as
>> much support for Perl on this mailing list as it's not as popular,
>> but
>> most issues you will probably encounter will be at the design level
>> rather than at the CPAN module level. Our working assumption is that
>> if
>> the DBI module looks like it has an issue, it doesn't and it's our
>> code.
>> Pretty much the same as for SQLite. Whilst I know that SQLite has had
>> bugs, the chances of us finding them is minimal, so we assume it's
>> our
>> code again.
>>
>> However I can say that we use Perl for all our system code and use
>> SQLite within it and its fast and easy.
>>
>> If you're using Perl, I would also recommend the following other
>> modules, this is a direct pull from our code. I've removed our code
>> specific modules.
>>
>> ```
>> #!/usr/bin/perl -w
>>
>> use strict;
>> use warnings;
>>
>> use Switch;
>> use DBI;
>> use JSON;
>> use Getopt::Long;      <-- Easiest way to get command line args in
>> and
>> processed.
>> use Data::Dumper;      <-- Utterly essential, don't leave home
>> without
>> it.
>> use Mojolicious::Lite; <-- Only need if you making a REST based
>> server
>> use Mojo::Parameters;  <-- Only need if you making a REST based
>> server
>> use Mojo::URL;         <-- Only need if you making a REST based
>> server
>> use Mojo::Log;         <-- Only need if you making a REST based
>> server
>> use REST::Client; <-- More rest based stuff
>> use Sereal::Encoder qw(encode_sereal sereal_encode_with_object);
>> use Sereal::Decoder qw(decode_sereal sereal_decode_with_object
>> scalar_looks_like_sereal);
>> use DateTime;          <-- Manage date and time properly.
>> use Net::Curl::Easy;
>> use Crypt::Random qw( makerandom );
>> use Log::Log4perl qw(get_logger :levels);
>> use File::Path qw(make_path); <-- Quick and easy way to make paths
>> and
>> directories.
>> use Net::Address::IP::Local;  <-- Easy way to manipulate IP
>> addresses.
>> use File::Slurp; <-- Quick and easy way to read and write files.
>> use Clone 'clone'; <-- You'll use a lot of objects and structures.
>> Copy
>> them properly and fast.
>>
>> ```
>>
>> We use Log4Perl a lot as it's easy to get things setup and then you
>> can
>> modify one log file and get easy changes. Also we use Mojolicious for
>> all the REST code wrapping.
>>
>> These are tried and tested CPAN modules that we know just work for us
>> and are a standard part of every new build. The most awkward one if
>> the
>> curl one, there seems to be a lot of versions of the Curl::Easy stuff
>> which simply don't work on Mac OS X (or other OS's).
>>
>> Just my 2p worth.
>>
>> Rob
>>
>> On 3 Apr 2019, at 0:21, Tom Browder wrote:
>>
>>> On Tue, Apr 2, 2019 at 17:30 [hidden email] <[hidden email]> wrote:
>>>
>>>> You might want to import everything into SQLite Studio or SQLite
>>>> Suite I
>>>> forget the exact name)--a freebie on the internet. I found it
>>>> worked
>>>> for
>>>> me. In order to help others, I would appreciate it if you tell us
>>>> on
>>>> this
>>>> usergroup how you made out. May it work for you. Peace! Alex
>>>
>>>
>>> Thanks for the idea, Alex.
>>>
>>> I'm doing all programmatically at the moment (using a Perl 6 module:
>>> DB::SQLite), but using one of those tools you mentioned would help
>>> in
>>> design for sure!
>>>
>>> -Tom
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
In reply to this post by Rob Willett
On Wed, Apr 3, 2019 at 02:08 Rob Willett <[hidden email]>
wrote:

> Tom,
>
> We use the Perl DB::SQLite module. It works very well and I cannot
> recall a single issue with it in the last four years. There's not as
> much support for Perl on this mailing list as it's not as popular, but
> most issues you will probably encounter will be at the design level

...

Thanks, Rob, good to know.

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I get help with db design for SQLite use?

Tom Browder
In reply to this post by Shawn Wagner
On Wed, Apr 3, 2019 at 02:50 Shawn Wagner <[hidden email]> wrote:

> Yay Perl! My favorite language. DBD::SQLite is definitely one of the
> better, fuller featured sqlite bindings out there. Though Tom is using
> perl6, not perl5. I have no idea how its version compares.

...

Glad to hear, Shawn, and the Perl 6 version is looking that way, too.

You Perl people really should look into Perl 6. I started with Perl 4 back
in 1993, went
to Perl 5 later than I should have (and used it to execute SQLite2 when I
first
heard about it), and ran with that until 2015 when I saw Perl 6
was almost ready for its first stable release.

I quickly realized Perl 6 is the Perl I envisioned, and have rarely gone
back
except to port favorite old scripts to Perl 6.

I invite you to visit <https://perl6.org> and look around. The community is
as
welcoming as this one has been to me.  The real place to hang out is the
#perl6 IRC channel.

(At the risk of going off topic, File::Slurp has issues. File::Slurper is a
> better alternative.)


By the way, in Perl 6, slurp is built-in and works great.

Best regards, and Happy Perling!

-Tom
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users