Re: [sqlite] "Cheating" at making common tables
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 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 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
Re: [sqlite] "Cheating" at making common tables
On 5 Aug 2018, at 2:40am, Stephen Chrzanowski 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Cheating" at making common tables
On Sat, 4 Aug 2018 21:40:53 -0400, Stephen Chrzanowski
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Cheating" at making common tables
On Saturday, 4 August, 2018 20:01, Stephen Chrzanowski 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Cheating" at making common tables
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 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Cheating" at making common tables
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
