Re: [sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
On 11/12/07, Andreas <[EMAIL PROTECTED]> wrote: > Am 12.11.2007 um 14:40 schrieb Igor Tandetnik: > > I wonder - do you have any plans to eventually drop some of these > > views? Or are you just going to keep creating them, exploding the > > size of the database schema? In fact, why on earth do you need so > > many views in the first place? > hmm, honestly i planned a ON DELETE-Trigger as soon as the ON INSERT- > Trigger works ;-) to sum it up : i expect to gather 25-30 rows in the > config-table and ~200-250 corresponding rows in the files table. That > makes at worst 7500 rows in the files-table. That said, i see myself > far away from performance-issues. My question belongs to the area > 'research-while-developing' or maybe howto place as much logic into > the DB, not polluting my apps-source lines with SELECT-staements. I'm > used to do things dynamically and usually use OODB's with python. For > this tiny project it's JS and sqlite and things work different here - > but it's gone work. In general, DDL should not be a common operation in an SQL database. The SQL schema should be fixed, using a relational model that fits what you need to do with your data, and queries do the actual data manipulation and retrieval. In sqlite terms, those VIEWs will actually come at a performance cost: every time the database is opened, sqlite must parse the schema to determine the layout of the database. There is no performance to be gained from using them, and the example above does not appear to simplify application queries at all (which is the entire point of using VIEWs). You've simply exchanged a column identifier for a table identifier. As you said, you're not yet near performance issues, and there's obviously room to do whatever you want, the above is just general guideline. I'd call creating such VIEWs as the above to be bad design, unless there's a detail you left out that makes them more appropriate. You mentioned using OODBs, so I think I understand where you're coming from, although I've never used anymyself. You might try to find what some common ORMs (Object-Relational Mappers) do. They're essentially middleware that converts between an object and SQL data. The "column=foo" bit is the type of thing they do behind the scenes. Your project sounds small enough that I would probably just bite the bullet and use SQL myself, rather than trying to use some abstraction layer. It'll take some mental adjustment, but it's probably worth it just to learn the SQL way of doing things. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
Am 12.11.2007 um 14:40 schrieb Igor Tandetnik: There is no syntax in SQLite to create a view (or anything else) with name generated at runtime from field values. A view name is a plain identifier hardcoded into the statement - it is not evaluated as an expression. great, thx for this quick clarification. What you can try is call a custom function from the trigger (see sqlite3_create_function), which function can then create a view. The function can use string manipulation to generate an appropriate SQL statement from the values of its parameters. ok, maybe later - something tells me maybe simple SELECTS within my app could do it, too. I wonder - do you have any plans to eventually drop some of these views? Or are you just going to keep creating them, exploding the size of the database schema? In fact, why on earth do you need so many views in the first place? hmm, honestly i planned a ON DELETE-Trigger as soon as the ON INSERT- Trigger works ;-) to sum it up : i expect to gather 25-30 rows in the config-table and ~200-250 corresponding rows in the files table. That makes at worst 7500 rows in the files-table. That said, i see myself far away from performance-issues. My question belongs to the area 'research-while-developing' or maybe howto place as much logic into the DB, not polluting my apps-source lines with SELECT-staements. I'm used to do things dynamically and usually use OODB's with python. For this tiny project it's JS and sqlite and things work different here - but it's gone work. @Joe : welcome to hackland ;) i've seen that approach in threads about 'cascading | recursive'-triggers on this list and assumed a solution to my tiny prob. thx for the example, i'll keep it till the next rainy sunday-afternoon comes - i'm sure i'll find a way to make the main-schema explode somehow ;-) greets, andreas - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: a TRIGGER to create a VIEW ? (newbie)
Andreas wrote:
I'm trying to let a Trigger create a View. The first Trigger
('resetSyncMarker') cares for unique-keying in the config-table. The
second Trigger('makeView') shall create a View on insert into the
config-table. The View's name shall be the content of the inserted
shortName-field.
There is no syntax in SQLite to create a view (or anything else) with
name generated at runtime from field values. A view name is a plain
identifier hardcoded into the statement - it is not evaluated as an
expression.
What you can try is call a custom function from the trigger (see
sqlite3_create_function), which function can then create a view. The
function can use string manipulation to generate an appropriate SQL
statement from the values of its parameters.
I wonder - do you have any plans to eventually drop some of these views?
Or are you just going to keep creating them, exploding the size of the
database schema? In fact, why on earth do you need so many views in the
first place?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
