"Cheating" at making common tables

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

"Cheating" at making common tables

Stephen Chrzanowski
I'm making a small database for a game, and a bunch of the tables follow
the same kind of naming convention due to normalization, like

{Name_Of_Information}s
  {Name_Of_Information}ID as Integer
  {Name_Of_Information}Name as Char

So for example:

Resources
  ResourceID as Integer
  ResourceName as Integer

(Plural name on the table name, singulars on the field names)

Would there be a way within SQLite via CTE or whatever other magic there
is, to create tables based on this structure, and setup the PK?

One of the tables that would link to a series of tables looks like:

SolarSystems
  SolarSystemID as integer
  SolarSystemName as char
  LifeFormID as integer
  EconomyTypeID as integer
  EconomyRankID as integer
  ConflictID as integer

Probably by the time someone presents their magic (again!) I'll have all
these small tables created by hand, but, going forward, it'd be something
nice to have on the tool belt.

Thanks!
_______________________________________________
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: "Cheating" at making common tables

Stephen Chrzanowski
I was right.  I got the tables done before a response.  But still would
like to know if there's a SQLite method of doing so.

My method was to use a templating application that I wrote at work. I give
it this variable declaration:

Name=Resource

I then give it this text:

CREATE TABLE [%(Name)s]( [%(Name)ID] INTEGER PRIMARY KEY AUTOINCREMENT,
[%(Name)Name] CHAR NOT NULL, UNIQUE([%(Name)Name]));
CREATE UNIQUE INDEX [u%(Name)Name] ON [%(Name)s]([%(Name)Name]);

It then gives me this result:

CREATE TABLE [Resources]( [ResourceID] INTEGER PRIMARY KEY AUTOINCREMENT,
[ResourceName] CHAR NOT NULL, UNIQUE([ResourceName]));
CREATE UNIQUE INDEX [uResourceName] ON [Resources]([ResourceName]);

Repeat for each simple table I want, and things were done in just a couple
of minutes.  Its a very basic template engine (Automatic Search & Replace
until no keyword strings exist), but it takes big chunks of time off when
we do upgrades to our 100+ servers around the world.

BUT, if I could have the SQL version be provided a list of names, it goes
and loops through repeating whatever processes I need based on that name
for that loop, and creates the structures I'd need later on in life. ;)
_______________________________________________
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: "Cheating" at making common tables

J Decker
I did this sort of thing in Xperdex (
https://sourceforge.net/projects/xperdex/ ) which is a C# thing, and
enabled easy creation of DataTables similarly auto creating ID and Name by
stripping pluralization from the name.
Was working on a similar thing for JS; but keep getting distracted making
it more of a schema layer for graph databases instead.  (
https://github.com/d3x0r/rdb-dataset )  (singularlize
https://github.com/d3x0r/rdb-dataset/blob/master/rdb-dataset.js#L53  (for
english))

But when I presented the utility of the methods; noone in the group I was
working with could concur on the automated methods; claiming I shouldn't
strip 's' off of 'games' and it should be 'games_id' and 'games_name' ..
and like 'sessions_game_groups_games_id'  *shrug* I just mention this,
because I doubt you'll ever get such a generic utility from sqlite... (or
any other database) but will be a layer you'll have to maintain in your own
libraries...

On Sat, Aug 4, 2018 at 7:00 PM Stephen Chrzanowski <[hidden email]>
wrote:

> I was right.  I got the tables done before a response.  But still would
> like to know if there's a SQLite method of doing so.
>
> My method was to use a templating application that I wrote at work. I give
> it this variable declaration:
>
> Name=Resource
>
> I then give it this text:
>
> CREATE TABLE [%(Name)s]( [%(Name)ID] INTEGER PRIMARY KEY AUTOINCREMENT,
> [%(Name)Name] CHAR NOT NULL, UNIQUE([%(Name)Name]));
> CREATE UNIQUE INDEX [u%(Name)Name] ON [%(Name)s]([%(Name)Name]);
>
> It then gives me this result:
>
> CREATE TABLE [Resources]( [ResourceID] INTEGER PRIMARY KEY AUTOINCREMENT,
> [ResourceName] CHAR NOT NULL, UNIQUE([ResourceName]));
> CREATE UNIQUE INDEX [uResourceName] ON [Resources]([ResourceName]);
>
> Repeat for each simple table I want, and things were done in just a couple
> of minutes.  Its a very basic template engine (Automatic Search & Replace
> until no keyword strings exist), but it takes big chunks of time off when
> we do upgrades to our 100+ servers around the world.
>
> BUT, if I could have the SQL version be provided a list of names, it goes
> and loops through repeating whatever processes I need based on that name
> for that loop, and creates the structures I'd need later on in life. ;)
> _______________________________________________
> 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: "Cheating" at making common tables

Keith Medcalf
In reply to this post by Stephen Chrzanowski

On Saturday, 4 August, 2018 20:01, Stephen Chrzanowski <[hidden email]> wrote:

>I was right.  I got the tables done before a response.  But still
>would like to know if there's a SQLite method of doing so.

Of course there is.

>My method was to use a templating application that I wrote at work. I
>give it this variable declaration:

There is not really that much difference between using program (a) -vs- program (b) to generate the text file containing the SQL statements.  I do not know why you would want to do it in SQL since that would still require a custom program, programmed in whatever language the custom program is going to be written in, plus writing the SQL itself -- effectively at least doubling (and likely more) the effort.  It might be "cool" but it is complicated and brittle.  KISS is sorely missing in computer programming these days.  I can do it in about 4 lines of Python which will take but a few seconds to write....

>Name=Resource

>I then give it this text:

>CREATE TABLE [%(Name)s]( [%(Name)ID] INTEGER PRIMARY KEY
>AUTOINCREMENT,
>[%(Name)Name] CHAR NOT NULL, UNIQUE([%(Name)Name]));
>CREATE UNIQUE INDEX [u%(Name)Name] ON [%(Name)s]([%(Name)Name]);

>It then gives me this result:

>CREATE TABLE [Resources]( [ResourceID] INTEGER PRIMARY KEY
>AUTOINCREMENT,
>[ResourceName] CHAR NOT NULL, UNIQUE([ResourceName]));
>CREATE UNIQUE INDEX [uResourceName] ON [Resources]([ResourceName]);

1) Why are you using AUTOINCREMENT?
2) The datatype CHAR does not exist.  The correct name is TEXT
3) Why are you creating a separate UNIQUE constraint rather than just specifying the column as UNIQUE?
4) Are you sure the text string is case sensitive for comparisons rather than merely case-preserving (that is, did you forget COLLATE NOCASE)?
5) You should not be creating duplicate UNIQUE indexes.

CREATE TABLE Resources (ResourceID INTEGER PRIMARY KEY, ResourceName TEXT NOT NULL UNIQUE);

is all you need.

>Repeat for each simple table I want, and things were done in just a
>couple of minutes.  Its a very basic template engine (Automatic Search &
>Replace until no keyword strings exist), but it takes big chunks of time off
>when we do upgrades to our 100+ servers around the world.

>BUT, if I could have the SQL version be provided a list of names, it
>goes and loops through repeating whatever processes I need based on that
>name for that loop, and creates the structures I'd need later on in life.
>;)

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
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: "Cheating" at making common tables

Kees Nuyt
In reply to this post by Stephen Chrzanowski
On Sat, 4 Aug 2018 21:40:53 -0400, Stephen Chrzanowski
<[hidden email]> wrote:

> I'm making a small database for a game, and a bunch of the tables follow
> the same kind of naming convention due to normalization, like
>
> {Name_Of_Information}s
>  {Name_Of_Information}ID as Integer
>  {Name_Of_Information}Name as Char
>
> So for example:
>
> Resources
>  ResourceID as Integer
>  ResourceName as Integer
>
> (Plural name on the table name, singulars on the field names)
>
> Would there be a way within SQLite via CTE or whatever other magic there
> is, to create tables based on this structure, and setup the PK?

I guess would use good old m4 for that. Or awk.

[...]

--
Regards,

Kees Nuyt

_______________________________________________
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: "Cheating" at making common tables

Simon Slavin-3
In reply to this post by Stephen Chrzanowski
On 5 Aug 2018, at 2:40am, Stephen Chrzanowski <[hidden email]> wrote:

> I'm making a small database for a game, and a bunch of the tables follow
> the same kind of naming convention due to normalization, like
>
> {Name_Of_Information}s
>  {Name_Of_Information}ID as Integer
>  {Name_Of_Information}Name as Char
>
> So for example:
>
> Resources
>  ResourceID as Integer
>  ResourceName as Integer

The name of a table should not be variable.  SQL has tables with fixed names and variable contents, and the entire support stack is designed to assist this.  So move the names of your tables, which are variable, into a table.

In the case of the above schema, the fix would be this:

TABLE InfoStore (
    infoType TEXT,
    ID INTEGER,
    name TEXT);

CREATE UNIQUE INDEX IS_InfoType_ID ON InfoStore (infoType, ID);

Assign an IDs for a new 'SolarSystem' row by calculating

1 + max(SELECT ID FROM InfoStore WHERE infoType='SolarSystem')

Everything goes into one big table which has a fixed name.  Magically you no longer need weird things like CTEs, and all access can be done by binding column values.

Simon.
_______________________________________________
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: "Cheating" at making common tables

Stephen Chrzanowski
Simon;

Interesting approach that I'd forgotten about.

The tables aren't "variable".  This is a "beginning of the project,
one-time execution" thing I was hoping to get at database initialization.
Meaning, 0-byte SQLite file size kind of initialization, with not a single
line of application code has been written.  I do want the tables to exist
and be static in the database file, and my application will reference those
exact table names.

I'm just looking for a shortcut that can trim down the time to do the
initial creation when I need to make 10 or so tables that have the exact
same structure, but different meaning for the content.  So basically
something I can whip out of a text file I have laying around somewhere, or
on a wiki I keep locally, paste it into my SQL editor of choice, change the
values in one place or on one line, press execute, poof, my tables are
created.  This would be executed after I've done the
pen-and-paper-proof-of-concept-schema design.

Obviously misunderstood by all, this whole post is more about using
different methodologies to get to the desired end result.  As Keith
mentions, a script can do this in a heartbeat, which is the true, but,
going CTE (Or other) routes may spark a new direction for me, or help
clarify something I may not quite understand right about CTEs.

That said, with your post below, you've reminded me that I'm actually using
this kind of methodology for an "Options" or "Preferences" database wrapper
for some of my applications that I share between machines.  This "Options"
database is a dedicated database file containing a single table with three
fields that have the machine name, the options keyword and the options
value as fields.  When the app looks for an 'option', the app does its look
up based on the machines name.  If the machines name doesn't exist, it'll
look for the same keyword substituting the hosts name as "DEFAULT".  If
that still doesn't find anything, then it goes and relies on the hard coded
default value.  It'll suck a tiny little bit when I run across a computer
with the name "DEFAULT" that actually uses this mechanism, but, the risk is
low, and even if it happens, the app still runs, pending hard coded
defaults blows something up I don't expect.

On Sat, Aug 4, 2018 at 10:52 PM, Simon Slavin <[hidden email]> wrote:

>
> The name of a table should not be variable.  SQL has tables with fixed
> names and variable contents, and the entire support stack is designed to
> assist this.  So move the names of your tables, which are variable, into a
> table.
>
> In the case of the above schema, the fix would be this:
>
> TABLE InfoStore (
>     infoType TEXT,
>     ID INTEGER,
>     name TEXT);
>
> CREATE UNIQUE INDEX IS_InfoType_ID ON InfoStore (infoType, ID);
>
> Assign an IDs for a new 'SolarSystem' row by calculating
>
> * 1 + max(SELECT ID FROM InfoStore WHERE infoType='SolarSystem')*


I'd rather a random 32-bit GUID and have the application die horribly due
to an almost impossible conflict, rather than offer a race condition like
this and successfully write wrong data.

The ID can be anything as it'll never be visible to the user.  It'll only
be visible to the code that needs to know how to update the data in the
database.  As I've spent a couple hours on this email alone (The rewrites..
ohhh the rewrites), I'm starting to fade with this thinking thing, but I'll
come up with something that works and isn't a possible race condition
probably on Monday (Out of town, out of internet service range, and I'm on
a week long vacation)


> Everything goes into one big table which has a fixed name.  Magically you
> no longer need weird things like CTEs, and all access can be done by
> binding column values.
>
> Simon.
>





On Sat, Aug 4, 2018 at 10:41 PM, Keith Medcalf <[hidden email]> wrote:

> 1) Why are you using AUTOINCREMENT?

2) The datatype CHAR does not exist.  The correct name is TEXT
> 3) Why are you creating a separate UNIQUE constraint rather than just
> specifying the column as UNIQUE?
> 4) Are you sure the text string is case sensitive for comparisons rather
> than merely case-preserving (that is, did you forget COLLATE NOCASE)?
> 5) You should not be creating duplicate UNIQUE indexes.
>
>
1) 100% guaranteed uniqueness, its an identifier my UI uses in list boxes,
combo boxes, text fields, and anything else that represents a reference to
a row within the database.  I'm limited to 32-bit Windows applications due
to the choice of **not* *spending $3k on a language and a 64-bit IDE I'm
comfortable with.  (But I will one of these days when any one of my
applications make more than $1,000/year, I'll have to.  .. so far, I'm up
to a cup of coffee worth... from a coworker who appreciated the timer I
wrote for them)  The UI components can take any signed (32-bit)-1 integer
(-1 represents NULL or unassigned as an object type and seemingly makes my
programs perform bad life choices when I reference that type of object as a
number).  My application will not ever control or change that auto-inc, and
immediately references the last_insert_id when needed right after the
insert statement update relevant UI elements if required.  Since I can play
with numbers 1 through 2,147,483,647 before I break something, this fits my
needs perfectly.  Simple, tidy, the database handles it, my application
understands the rules around what the database is doing so it doesn't need
to do anything directly with it.

2) Yep, I know CHAR isn't something that is known by SQLite.  I'm just used
to CHAR or VARCHAR from the MSSQL days of 15 years ago, and I just slapped
it in as the type ever since.  For this app, naming every single field as
the type "gobbledygook" (Wow... Spell check has that word!?) would be
satisfactory for me in this particular program, as this code will never
leave this computer, and my reading the code three years from now would
only make me chuckle.  TEXT was something I avoided in MSSQL and I just
never went back to it with SQLites non-interest in field type
declarations.  I know... this kind of naming convention (Referring to using
TEXT instead of CHAR) makes peoples eyes itch.....

3) I used SQLite Expert to create the tables instead of my writing out the
command.  There's two ways I can declare a field unique, and I must have
specified both without realizing it.  It looked funny, but, thought "meh".
I had SQLite Expert drop the unique statement, and its now contained in the
table schema only.

4) The actual text in the mini-tables won't ever be compared against.
It'll all be drop downs and compared against based on the IDs in #1.
However, I do catch your drift that when I do enter new text, "This
Resource" would be considered different than "this resource", which means
the unique constraint I want wouldn't throw the exception, but be submitted
to the database.

5) I agree.  I don't like redundancy like that. Fixed in #3 as I don't like
redundancy like that. (haw-haw?)

There is not really that much difference between using program (a) -vs-

> program (b) to generate the text file containing the SQL statements.  I do
> not know why you would want to do it in SQL since that would still require
> a custom program, programmed in whatever language the custom program is
> going to be written in, plus writing the SQL itself -- effectively at least
> doubling (and likely more) the effort.  It might be "cool" but it is
> complicated and brittle.  KISS is sorely missing in computer programming
> these days.  I can do it in about 4 lines of Python which will take but a
> few seconds to write....
>
>
Fully agree.  Evidence is that I made the 10 or so tables using a program I
wrote for other nefarious reasons.  I could have also written the
application that will be controlling this database to do all the work for
me as well if the tables don't exist at first run (Not a bad idea,
really).  The reason for the post wasn't for efficiency matters, but just
another avenue to understand how CTEs work, or apply other methodologies to
get to the same result at the end of the day.

.. and I now know why my boss laughs at my emails for something so
simple... {chuckle}
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users