Re: [sqlite] RFE: Rename Column
Hi, On Sun, Jan 05, 2014 at 22:58:31 +, Simon Slavin wrote: [...] > You're both right. Igor's statement pretty-much /is/ the > reason one cannot rename a column. One would need to > write a parser and changer for SQL statements that could > identify and change column names in many statements with > all sorts of weird possibilities for formatting. Isn't most of the parser already existant, because sqlite must parse the in-db-schema-sql to know the structure of the database? And there already must be code to identify column names in the parsed structure, for exactly the above named reasons (checking constraints, checking foreign keys)? Obviously, the parser is not a parser-and-changer. Agreed. What about this simple solution: Let "alter table rename column" parse the schema, search for the column name. If it's in no constraint, etc, then do the rename in the sqlite_master table. If the column is used anywhere, error out (which is not worse than before). This will at least stop people from creating databases, that can't be opened after mangling around in the sqlite_master table. As originally stated, my aim is not a full blown alter table implementation right away, but only renaming (possibly unreferenced) columns. > Two alternatives: (a) actually write the > parser-and-changer that processes SQL commands, or For creating the actual changer, one might use the already parsed in-memory-structure, change it, and dump it back out as sql. That might break formatting for people who love it, but that's still lots better than doing nothing. Being able to dump the internal structures as sql might also be helpful for debugging and writing test cases. > (b) wait until the major file format changes in SQLite4, > then change the way SQL stores the CREATE commands needed > to construct a database so it stores a structured version > of the commands instead of the raw text. The "structured version" could be the dumped-sql from above? But of course, sqlite4 could use some fancy structured format for that as well. But why invent a new (binary) format, when we have a text based format at hand, that can do everything needed? > The advantage of either change would be that it allows > almost all the ALTER TABLE commands SQL users expect, not > just changing column names. If that's true, that would be great! > This simplifies life not just for normal users but also > for all the writers of SQLite GUI managers out there, Simplifying the world for everyone would be awesome. Like the foreignkeys are awesome. Instead of everybody having to create (possibly wrong) triggers, those triggers are now builtin. > who have to write nasty risky time-consuming hacks if > they want to accomplish those operations. I am especially afraid of the risky part. That's not the reliable attribute sqlite is known for. That's why my suggestion from above is to start with simple operations, and if sqlite can't do them reliably (because it would break something), then error out. That would even improve the gui managers: They could try the sqlite internal variant (reliable), then ask the user "Okay, the reliable method did not work, I have some fallback methods, which are not as reliable, but used to work most of the time. Should I apply those?" > Simon. Cheers Elrond ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RFE: Rename Column
Hi everybody, Short: Could you implement alter table rename column? I know that sqlite does only support a limited subset of SQL. Some subsets have only been added lately, like foreign keys, and can be disabled at compile time to safe space. Renaming a table is already supported, which is good. I hope, renaming a column should not be extremely complex. There are even dangerous recipes out there [1] that manipulate the internal structure (sqlite_master) using the public API. So could those recipes be turned into (hopefully safer) internal code? Cheers Elrond [1] http://stackoverflow.com/a/6684034/1890086 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite performance with sizeable tables
On Wed, Mar 08, 2006 at 03:09:15PM -0800, [EMAIL PROTECTED] wrote: [...] > One question though: are the file access "sorted", so that seeks are > minimised when performing a transaction (making the assumption that the file > is not fragmented on disk)? I assume you tried your tests with "PRAGMA synchronous = OFF;"? And I presume you're aware, that this pragma is risky in the face of os crashes and power failures. Elrond
Re: [sqlite] Re: large table performance
On Fri, Mar 03, 2006 at 04:00:58PM +0100, Daniel Franke wrote:
[...]
> > Finally, parsing a few million INSERT statements is very fast
> > in SQLite but it still takes time. You can increase the speed
> > by a factor of 3 or more by using prepared statements, if you
> > are not already.
> As I understand it, sqlite_prepare() and friends will speed up
> statements that can be reused. After the initial parsing of the data
> file(s), all I got is a looong string of INSERT statements. Then, each
> statement is used exactly once.
[...]
The idea is to
prepstm = prepare("insert into table values(?, ?)");
The "?" are vairables. You can assign to them and then run
the "compiled" (prepared) statement once for those values,
then set new values, run it again.
See sqlite3_bind_* and sqlite3_step for further details.
It might speed up your stuff a lot, as sqlite doesn't need
to parse the sql for each insert.
Elrond
Re: [sqlite] large table performance
While talking performance: Did anyone compare sqlite in a simplistic CREATE TABLE t(key BLOB PRIMARY KEY, value BLOB); scenario to other dedicated key/value DBs (like berkeley deb, gdbm, ...)? Elrond
Re: [sqlite] Running App state in db?
Thanks to everyone answering in this thread! On Wed, Mar 01, 2006 at 05:44:45PM -0800, Nathaniel Smith wrote: [...] > I don't know enough about your app to know if this would work, but > if you only have the one app instance managing locks, and locks expire > whenever that app dies... perhaps you can just have that app, on > startup, unconditionally erase all locks in the db? I'll do that probably anyway. (I'm considering killing the file and recreating the schema on "boot".) My app is a server and forks off some childs for the clients. So if the child crashes, those lock records should go away. Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 12:44:47PM -0600, Jim C. Nasby wrote: [...] > Depending on your needs, you might be able to just lock a row for > updates and hold that lock. IE, open a seperate connection to the > database and do: > > BEGIN; > UPDATE process SET start_time = now() WHERE process_id = ?; > > And then 'sit' on that connection until you're done. When you're > finished, just issue a COMMIT. Note that some databases won't like you > leaving that transaction open a real long time, so it depends on what > you're doing if this will work. I also don't know if SQLite cares about > such things. [...] sqlite mostly cares about such things, but it goes the "lock the whole db" way. I'd love row level locks, right! I already considered adding some "this_row_locked" column to a table, just to emulate row level locking. Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 04:00:53PM -0600, Jim C. Nasby wrote: > On Wed, Mar 01, 2006 at 01:53:45PM -0800, w b wrote: > > Well, my locking data isn't as simple as "locked, not > > locked". The resource has ranges that can be locked, and it > > can be locked for reading (shared) and writing (exclusive). > > It's not really fun. > > Sounds to me like the best bet is to put the 'resources' into a database > and let it handle the locking... Letting the db handle it, isn't really an option: For starters, I can't put any possible range of the resource into the db. I intended to only put the locks as such in the db. When it comes to a new lock, I'll select all relevant old locks, that might conflict, handle the conflict check in app logic and finally insert a new lock record. (all inside a proper table lock/transaction). I just hoped for some cool way to let the db cleanup all those records, when the session dies. (not, that I have that now ;) ) Elrond
Re: [sqlite] Running App state in db?
On Wed, Mar 01, 2006 at 01:32:31PM -0600, Jim C. Nasby wrote: > BTW, if you're running everything on a single machine there's lots of > other ways you can do locking that don't involve the database. [...] Well, my locking data isn't as simple as "locked, not locked". The resource has ranges that can be locked, and it can be locked for reading (shared) and writing (exclusive). It's not really fun. That said, I want the whole fun to work on Unix and Windows. So my options are: 1) Write native api code for the job, using shared memory/mutexs, or whatnot for the relevant OS. 2) Find a suitable storage for the structured data. I'm currently trying (2). Elrond
[sqlite] Running App state in db?
Hi, I'm considering to put the state of a running app into an sqlite db. I want it in a db, so external tools can query it and know, what the app is doing currently. Any hints on how to clean up the db, when the app crashes? (I have external resources, that I need to "lock", so the idea is to put the locks in the db, so more than one instance of the app can run and they don't kill the external resource.) Any hints? Elrond
