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 <slav...@bigfraud.org> 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 <kmedc...@dessus.com> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
