Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

If that were true, then I wouldn't be getting a very large speed-up when
enveloping write ops in an explicit transaction, would I?


On Mon, Mar 3, 2014 at 8:44 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 4 Mar 2014, at 1:15am, romtek <rom...@gmail.com> wrote:
>
> > I have a question based on my observation. According to your numbers for
> a
> > 5400 RPM disk, one write op should take about 11 ms. However, it often
> > takes only about 1 ms on HostGator drives on its shared hosting servers.
> > Are there drives that are SO much faster than 5400 RPM ones?
>
> I'll bet that the hosting servers are virtual machines and the drives are
> virtual drives, not physical drives.  Everything is actually done in RAM
> and just flushed to physical disk every so often.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

Thank for the clarification, Simon!

I have a question based on my observation. According to your numbers for a
5400 RPM disk, one write op should take about 11 ms. However, it often
takes only about 1 ms on HostGator drives on its shared hosting servers.
Are there drives that are SO much faster than 5400 RPM ones?


On Mon, Mar 3, 2014 at 5:57 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 3 Mar 2014, at 9:11pm, romtek <rom...@gmail.com> wrote:
>
> > Simon, does a real disk have to be a rotating hard disk? Is there
>  problem
> > with SSDs as far as SQLite is concerned?
>
> SSDs aren't a problem, and SQLite works fine with them, but they change
> the timings associated with SQLite a great deal.  Simplified explanation
> follows.
>
> With rotating hard disks each read or write operation has to wait for the
> disk to rotate to the right place.  And if you have your files
> defragmented, all of a SQLite database is clumped up together on disk right
> next to the journal file for that database.  An END TRANSACTION command can
> involve six (more ?  anyone ?) accesses of database or journal file, and if
> all the parts of disk you're writing to are near one-another that can
> involve six rotations of the hard disk.  So ...
>
> Hard disk rotates at 5400rpm = 90rotations/s .
> 6 reads or writes to disk, assuming no skipped rotations, takes 66ms =
> 1/15th of a second .
> A loop doing 100 INSERTs can take over six seconds !
>
> This means that in real life SQLite can be faster if you do /not/
> defragment your drive.  Because you don't have to wait for a full rotation
> every time.
>
> SSDs, of course, don't have to wait for anything physical.  The
> improvement in speed gained by using transactions does not work the same
> way: there are still fewer operations, so it should still be faster, but
> not by as much.  My original statement about so much of the time being
> spent on the END TRANSACTION no longer holds.  That's why I asked.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

>
> a normal DB where writing does not happen often I would suggest a SSD...
> but anything with a very active read/write cycle is best avoided


If you were to quantify this, would you say that "active" starts at once
per second, many times per second, once a min or...?


On Mon, Mar 3, 2014 at 3:47 PM, RSmith <rsm...@rsweb.co.za> wrote:

>
> On 2014/03/03 23:11, romtek wrote:
>
>> Simon, does a real disk have to be a rotating hard disk? Is there  problem
>> with SSDs as far as SQLite is concerned?
>>
>
> No, what Simon is trying to point out is simply that the write performance
> experienced by L. Wood might be because journal writes might be synced to
> hard disk (in rotating disk cases anyway) and as such cause delays all
> throughout the transaction even if they are not holding up the final
> commit.  Not because this is in error or wrong in any way, simply as trying
> to explain why he sees the performance spread he sees.
>
> Other reasons might be excessively large binds causing the cache to spill
> or simply have memory writes taking so long that it seems to cause
> inter-transaction delays, although if that were the case one would expect
> the final commit to take a lot longer even.
>
> My money is still on the specific pragmas used which might be forcing
> syncs or non-ACID operation. We would need to have the DB schema and the
> typical query to really test why it works the way it works in his case.
>
>
> One note on SSD's, they pose no physical problem to SQLite, and in fact
> works magically fast, but having a DB which read/write a LOT of data on
> them is not really great since  the repeated read-write cycle of specific
> data areas tire down the typical NAND flash that makes up the SSD, even
> though modern SSDs may use MLC NAND or have firmware that tries to exercise
> every bit in memory equally so as to spread the write operations to avoid
> one piece of disk-memory dying quickly. Eventually though, when all bits of
> memory experienced upwards of 500K write operations (which is quite a
> while), it will fail... but you will have lots of warning. A read-only DB
> on an SSD drive cannot be beaten... even a normal DB where writing does not
> happen often I would suggest a SSD... but anything with a very active
> read/write cycle is best avoided - or at a minimum backed up by a good old
> rotating magnetic platter drive.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "INSERT" and "END TRANSACTION" time

[romtek]

Simon, does a real disk have to be a rotating hard disk? Is there  problem
with SSDs as far as SQLite is concerned?


On Mon, Mar 3, 2014 at 2:21 PM, Simon Slavin  wrote:

>
> On 3 Mar 2014, at 6:03pm, L. Wood  wrote:
>
> > _bind() on a prepared statement and execution of "INSERT" are taking 70%
> of the time, but the "END TRANSACTION" only 30% of the time.
> >
> > The time between _bind() and execution of "INSERT" is roughly 50/50, so
> it's a total of:
> >
> > _bind(): 35%
> > INSERT: 35%
> > END TRANSACTION: 30%
> >
> > I would have expected the execution of INSERT to taken almost 0% within
> a transaction. Why is this not the case?
>
> Have you used any PRAGMAs which defeat ACID ?  In other words, anything
> that speeds SQLite up ?
>
> Are you writing to a real file on a real rotating hard disk ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

[romtek]

Thanks, Simon. Interestingly, for this server, disk operations aren't
particularly fast. One SQLite write op takes about 4 times longer than on a
HostGator server.

I wonder if what I/you described also means that this file system isn't
likely to support file locks needed for SQLite to control access to the DB
file to prevent data corruption.


On Sun, Mar 2, 2014 at 9:18 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 3 Mar 2014, at 2:14am, romtek <rom...@gmail.com> wrote:
>
> > On one of my hosting servers (this one is a VPS), a bunch of write
> > operations take practically the same amount of time when they are
> performed
> > individually as when they are performed as one explicit transaction. I've
> > varied the number of ops up to 200 -- with the similar results. Why is
> that?
> > What could be about the file system or disk drive that could cause this?
>
> I'm betting it's a running on newer hardware more suited to virtual
> machines.  One of the problems with virtual computers is that their disk
> storage is often virtualised to a very high degree.  For instance, what
> appears to the computer to be disk storage may be entirely held on SSD, or
> on a fast internal disk, and flushed to a huge but slower disk just once a
> minute.  Or once every five minutes.  Or once an hour.  This is an
> efficient way to simulate 20 to 200 virtual machines on what is one lump of
> hardware.
>
> A result of this is that disk operations are very fast.  However, any
> 'sync()' operations do nothing at all because nobody cares what happens if
> an imaginary computer crashes.  Since most of the time involved in ending a
> transaction is waiting for synchronisation, this produces the results you
> note: syncing once takes the same time as syncing 200 times, because
> neither of them is doing much.  And a result of that is that if the
> computer crashes, you lose the last minute/minutes/hour of processing and
> the sync() state of database operations is suspect.
>
> Go read their terms and find out what they guarantee to do if a virtual
> machine crashes.  You'll probably find that they'll get a virtual computer
> running again very quickly but don't make promises about how recent the
> image they restore will be.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why would batched write operations NOT be faster than individual ones

[romtek]

In case this gives somebody a clue, the server in question is on
http://vps.net/.


On Sun, Mar 2, 2014 at 8:14 PM, romtek <rom...@gmail.com> wrote:

> Hi,
>
> On one of my hosting servers (this one is a VPS), a bunch of write
> operations take practically the same amount of time when they are performed
> individually as when they are performed as one explicit transaction. I've
> varied the number of ops up to 200 -- with the similar results. Why is that?
> What could be about the file system or disk drive that could cause this?
>
> P.S. My other servers (shared hosting on HostGator), batched writes take
> MUCH less time than individual write ops, as expected.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why would batched write operations NOT be faster than individual ones

[romtek]

Hi,

On one of my hosting servers (this one is a VPS), a bunch of write
operations take practically the same amount of time when they are performed
individually as when they are performed as one explicit transaction. I've
varied the number of ops up to 200 -- with the similar results. Why is that?
What could be about the file system or disk drive that could cause this?

P.S. My other servers (shared hosting on HostGator), batched writes take
MUCH less time than individual write ops, as expected.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Graphic SQLite Client Tool

[romtek]

I'd recommend http://www.sqliteexpert.com/. I've been using it for years
and like it.


On Sun, Jan 26, 2014 at 5:08 AM, big stone  wrote:

> Hello sqlite-users,
>
> I'm looking for a Graphic SQLite  Client Tool  to equip a classroom of  old
> windows PC.
>
> So far, the best option I found is :
> - dbeaver 2.3.6 (multi-motors : SQLite, Mysql, Postgresql)
> - with the latest Xerial driver sqlite-jdbc4-3.8.2-SNAPSHOT.jar (with
> SQLite3.8.2)
>
>
> Has anyone knowledge and practice of a better option ?
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] When does SQLite DB file get locked?

[romtek]

Hi,

I started writing this message and then read
http://www.sqlite.org/lang_transaction.html, and I think I've gotten my
answer, but I will ask the question to be absolutely certain, particularly
when using SQLite with PHP (using PDO).

Probably based on flawed understanding of how SQLite worked, I've spent
(wasted, I see now) my time to create a library for working with my DBs
with an intention to minimize the time a script has an open connection to a
DB file. And I've tried to make sure that I close connections as soon as
possible. The document linked to above says that having a connection or
executing a regular BEGIN statement doesn't create a lock. So, if my new
understanding is correct, I should only be concerned with shortening
execution of code within transaction blocks and not at all with how long a
connection is held by a script. Is this correct?

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Copying a DB file that is being in use

[romtek]

Hi,

A recent discussion of a reader/writer problematic situation has
prompted me to ask the following question.

While the database file is being written to, the data isn't ready to
be used by readers, so that DB is locked. What happens if I initiate
copying of this file (e.g., to back it up)? Is the file being
protected by the OS from being copied while it's being written to? Or
do I need to make sure that nobody uses the DB before I attempt to
copy the file?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

Alexey, I am not clear about whether you reported my bug or yours. Do
you mind giving me a link to it so that I could see if I need to
report mine and let it go?



On Sun, Dec 25, 2011 at 11:16 AM, Alexey Pechnikov
 wrote:
> I think there are some problems with dynamic datatypes in sqlite.
> This may be redesigned in SQLite4 but not in SQLite3.
>
> P.S. Datatypes recognized differently after Shift-Insert SQLite3
> commands into Tcl shell. This is similar to problem with datatypes
> in triggers. I did report bug but for backward compability problem
> wil not be resolved by upstream. So I did write patch for myself.
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

So, you also think this is a bug? I will submit it to the SQLite bug database.


On Wed, Dec 21, 2011 at 7:42 AM, Alexey Pechnikov
 wrote:
> The problem can be fixed by variables bindings patch:
> http://sqlite.mobigroup.ru/wiki?name=tclsqlite
> I think, you can do same for you lang.
>
> --
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about the way put a database of SQLite 3 beside web folder?

[romtek]

On Fri, Dec 16, 2011 at 8:31 AM, Simon Slavin  wrote:
> I would not advise putting it in a folder that you are serving to the web 
> since this would allow anyone browsing yourweb site to inspect the full 
> contents of the file.


One could easily configure the server to *not* serve database files
(or contents of folders) to a site's visitor. Therefore, it could be
located anywhere.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange behavior for timeouts in transactions

[romtek]

On Tue, Dec 20, 2011 at 6:08 AM, Simon Slavin  wrote:
>
> First, there's more than one way of using SQLite3 from PHP.  There's also the 
> interface SQLite3:: which is a much thinner wrapper around the basic SQLite C 
> library.  I have no way of knowing what proportion of programmers use one 
> library or the other.

I started using SQLite with PHP in version 5.2.x, when there wasn't
SQLite3:: interface, so I chose to use PDO. Moreover, I don't want to
lock myself into SQLite, PDO provides a somewhat more flexible
approach.


> As long as you set an acceptable timeout, which you do with one of these 
> calls depending which library you're using
>
> 
>
> 
>
> your web service probably isn't going to run into problems.  What might be 
> worrying you right now is the result of the default timeout being zero, 
> something which is arguably weird.  In other words, unless you specify a 
> timeout yourself any report of a lock is instantly treated like an error.  
> Set your timeout to one second or five seconds and the behaviour will be more 
> reasonable.

I'd done some research into this since I asked my question and learned
some things. According to http://bugs.php.net/bug.php?id=38182 and
http://www.serverphorums.com/read.php?7,118071, PDO_SQLITE defaults to
a 60 second busy timeout. This should be enough.

> Second, most transactions and locks in SQLite from PHP are fleeting.  
> Generally you want your web page to list some records or do one update.  You 
> wouldn't have a process keep a lock active for a long time because this would 
> correspond to your server taking a long time to show a web page, and nobody 
> wants that.  So a lock by one process probably isn't going to last very long 
> -- probably a fraction of a second.  Access for a web page is unlikely to 
> involve deadlock because of the nature of web pages: they generally don't 
> interact with the user while a transaction is open.


One would think so :) But Drupal people have run into the opposite
(http://drupal.org/node/1120020 and
http://stackoverflow.com/questions/6108602/avoiding-locked-sqlite-database-timeouts-with-php-pdo).
This seems to happen for them mostly when they are indexing their data
a visitor to a site requests a page. Apparently, their indexing tasks
is long running and write intensive. I think they should rework this
tasks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange behavior for timeouts in transactions

[romtek]

On Mon, Dec 19, 2011 at 1:34 PM, Pavel Ivanov  wrote:
>
> Yes, this is expected behavior. In this case transaction won't be able
> to ever proceed because it can proceed only when writing transaction
> in session 1 is committed but it cannot be committed until all reading
> transactions are finished, including transaction in session 2. So it's
> a deadlock and SQLite knows about that, thus it returns "database
> locked" immediately.


Does anyone know if those who use SQLite with PHP need to be concerned
with this issue (i.e. to program around this)? I've assumed that a PDO
driver for SQLite or another layer will take care of waiting,
retrying, etc. when needed. Is that a correct assumption?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

On Mon, Dec 19, 2011 at 11:23 PM, Roger Binns <rog...@rogerbinns.com> wrote:
>
>
> On 19/12/11 20:31, romtek wrote:
> > As you can see, isActive is declared as an integer in table2,
>
> > This type of thing worked for years with an older version of SQLite
> > library
>
> Are you sure?

Absolutely. I have the same DB file (same schema but different data)
on my dev server and production server. Both were using PHP 5.2.x
until several days ago, and everything was working fine on both of
them. For years! When I switched to PHP 5.3.8 on my dev server, the
trigger was no longer working, and as soon as I switched to PHP 5.3.8
on the production server, the same thing happened. The fix was to
change the comparison to an int instead of to a string in the WHEN
clause.


> > .. that is used in PHP 5.2.17 ..
>
> PHP used to use SQLite 2.  The internals of SQLite 2 were that everything
> was stored as a string where this kind of comparison would work.

Old versions -- yes, but the PDO SQLite extension I was using was
using version 3.x. PHP 5.2.17 was using exactly SQLite lib 3.3.7. So,
maybe that version also stored everything as a string?


> Anyway this shows that you definitely don't get strings and numbers being
> equal to each other:

I do in SELECT statements. Consider this working example:

CREATE TABLE [table3] (
  [name] VARCHAR(20),
  [age] INTEGER);

The following two queries produce the same results in SQLite version 3.7.9:

SELECT * FROM table3 WHERE age=10;
SELECT * FROM table3 WHERE age='10';

So, comparisons in SELECT statements aren't performed the same way
they are in triggers. Triggers apparently use stricter type checking.
This has changed in some version in the 3.x line, and I am concerned
that this change was yelled about.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in trigger: when comparing a value of an int column to a quoted value

[romtek]

Hi,

I would like to know if I am dealing with a bug or a backward incompatible
change that I am not aware of.

Consider this example:

CREATE TABLE [table2] (
  [id] INTEGER NOT NULL ON CONFLICT FAIL PRIMARY KEY ON CONFLICT FAIL
AUTOINCREMENT,
  [isActive] INTEGER NOT NULL ON CONFLICT FAIL DEFAULT (0));

CREATE TRIGGER [update_table1]
AFTER INSERT
ON [table2]
FOR EACH ROW
WHEN new.isActive='1'
BEGIN
INSERT INTO table1 (name) VALUES("dog");
END;

As you can see, isActive is declared as an integer in table2, and if I
specify the comparison in the WHEN clause of the trigger as
new.isActive='1' (with the quote characters), the trigger doesn't get
activated when I expect it to be activated (when I insert a record into the
table with 1 as a value in column isActive).

This type of thing worked for years with an older version of SQLite library
(specifically, 3.3.7, and possible older, that is used in PHP 5.2.17), but
doesn't work with version 3.7.7.1 (and possible some earlier versions too)
or newer.

I've always thought that because SQLite didn't enforce data types, I could
do what I have in the example, and this has worked! So, is this a bug in
more recent versions of SQLite or an intended change that I am unaware of?

Roman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance of getting selected columns vs getting all columns

[romtek]

Hi,

I used to think that getting selected fields from a relational DB is faster
than getting all fields, so I used to carefully selected only those that I
needed for a particular task. However, this is time-consuming (for a
developer) and error prone. So, I've relaxed my rules on occasions. And
also, in some presentation by the lead (I think) Drupal developer I heard
him say that we shouldn't bother with that and just use the *. Now, it is
possible that I misunderstood him or forgot what really happened because
it's been a long time ago, and maybe his recommendation applied to Drupal
specifically and maybe only in some situations... I don't know for sure.

I've just tested with a query I was working on with an SQLite management
tool and noticed that selecting all fields took at least *twice as long* as
selecting only two, and I don't have a huge number of columns (only 18). And
I am not sure if I was testing SQLite or this particular tool. (It is
possible that allocating memory for more fields is very stressful for this
tool.) So, I want to ask those who are more experienced with SQLite and also
with other relational DBMSs than I am: is it generally significantly faster
to select only what's needed than selecting all fields?

Roman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 10:00 PM, Roger Binns <rog...@rogerbinns.com> wrote:

> On 05/15/2011 07:48 PM, romtek wrote:
> > I was describing what I have to do during design time, which often
> happens
> > after a website has been launched and is operational.
>
> Huh?  You launch and have a site in production and then you design it?
>

I don't know about how things work in your world, but in mine requirements
for apps change sometimes, so I have to enter design stage after an app (may
be a website) has been released. Perhaps, you are a clairvoyant genius who
can foresee everything that would ever be required by your clients and also
produce defect-free software, but I have to work with my limitations, so I
modify my software as new requirements or bugs emerge :)



> You as a developer have a schema, triggers, indices, queries, templates and
> other code that all work together.  You also need need to have a plan for
> how changes are made (and rolled back) in production.
>
> SQLite does not come with a magic function that does this.  No one else has
> written anything that precisely meets your needs because every situation
> and
> code base is sufficiently different.
>

I've simply requested from SQLite devs to add an internal mechanism to
facilitate renaming columns, not a magic function. Simon has understood
exactly what's needed. Let's not make this issue into something that it's
not.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 10:05 PM, Nico Williams wrote:

> Right, well, SQLite3 doesn't support renaming columns.  Internally
> SQLite3 only stores the CREATE statements for all schema elements
> (plus actual b-trees for tables and indexes).  Specifically SQLite3
> does not store schema elements in any sort of parse and normalized
> form, which means that changing the names of schema elements that are
> embedded in others is quite difficult, as it would mean parsing the
> stored CREATE statements, editing them, then writing them back to the
> DB.
>

I think that if external SQLite management tools' developers have been able
to provide this feature, SQLite devs could add this feature too to make it
easier for everybody.



> If you must use Jay's method at all, I recommend that you do something
> like what Jay described, but, first take some steps to make sure you
> get the edits right:
>


I would just use a standalone tool to do this in order to save time and
prevent errors.



> I don't see an analogy there.  Here's one using Word and OpenOffice:


To you and another person who didn't agree with my analogy. You're analyzing
it on a the wrong level. My point was this: if there's an easier way to do
something, I'd prefer to do it that way. Let's not get into a rat's hole.



> > Secondly, if I executed the above SQL code, what would happen to
> triggers,
> > etc. that are associated with the original table?
>
> You have to fix your triggers and views -- that's what SQLite3 doesn't
> have the code to do.  It's missing.
>

Thanks. That's more support to justify using a tool that handles these
details automatically.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 5:22 PM, Simon Slavin  wrote:

> Yeah, you have to fake it by making a new table.  You want support for more
> ALTER TABLE variants.  The proper way to do it would be to
>
> ALTER TABLE myTable ADD COLUMN dateAdded
> UPDATE myTable SET dateAdded = date
> ALTER TABLE myTable DROP COLUMN date
>
> And SQLite doesn't support the third command.  Well, I'm sure it's on the
> 'to do' list.  If not, one of the devs can add it.
>


Yes, that is what I want. This will allow tool developers to add the ability
to rename columns to their tools easily.  SQLite Expert (
http://www.sqliteexpert.com/) and SQLite Maestro (
http://www.sqlmaestro.com/news/company/sqlite_maestro_11_3_released/)
already support this. Now I want at least one web based tool to have this
feature too. Adminer (http://www.adminer.org/) seems like a very good tool,
but its developer has refused to add this feature because SQLite doesn't
support an easy implementation of it.

So, I hope this feature gets added to the to-do list if it's not on it yet.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 5:27 PM, Roger Binns  wrote:

> Wanting to rename a column should be a rare thing since it is so rare.  If
> you are frequently renaming columns then your code will be very brittle.
>
>
I was describing what I have to do during design time, which often happens
after a website has been launched and is operational. I sometimes need to
add/modify features, and that sometimes requires that DB schema be changed.
So, I want the tool I use to let me do this easily.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 5:10 PM, Mr. Puneet Kishor wrote:

>
> > A table:
> >
> > id, eventName, date
> >
> > I want to rename date to dateAdded.
> >
>
>
> sqlite doesn't support changing the name of a table column (and, neither
> you nor your user should be doing this -- there is something strange with
> your app requirements).


I was describing what I have to do when I need to change a DB's schema.
Software isn't always perfect and sometimes needs to be changed.




> That said, you can "rename" a column by creating a new table with the new
> column definitions and copy data from the old table to the new table.
>
> CREATE TABLE new_table (id, eventName, dateAdded);
> INSERT INTO new_table (id, eventName, dateAdded) SELECT id, eventName, date
> FROM old_table;
>


Thanks to you and everybody else who's provided a sample of code to do this,
but I want to be more productive in my software development. An analogy: if
I want to create a letter with pretty styles and maybe images, I will use MS
Word or, at least, Open Office. I will not write a program to do that :)
So, I want a web based tool I use to allow me to quickly and easily rename a
column.

Secondly, if I executed the above SQL code, what would happen to triggers,
etc. that are associated with the original table?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need to be able to rename attributes

[romtek]

On Sun, May 15, 2011 at 4:39 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 15 May 2011, at 10:33pm, romtek wrote:
>
> > So, I am asking developers of SQLite to make it easy for tool developers
> to
> > offer the ability to rename attributes.
>
> The SQL specification does not use the term 'attribute' in any way that
> would give them names.  Can you explain what you mean by 'rename attributes'
> ?  Perhaps give an example.
>
> Simon.
>


OK, I will give you an example, and you correct my use of the terms, please.

A table:

id, eventName, date

I want to rename date to dateAdded.

People currently jump through hoops in order to achieve such a simple (from
the user's point of view) and needed goal:
http://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table
.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Need to be able to rename attributes

[romtek]

Hi,

SQLite probably was intended as an embedded database for standalone
software. However, because of its promotion by PHP community, it's been used
to power websites. I use it for that purpose too and prefer it over other
SQL DB solutions (primary because it allows me to move/backup data from a
server to server easily.) However, in certain situations, I feel that I've
shot myself in the foot by having chosen SQLite, and the reason is that it
doesn't support renaming attributes.

Some standalone tools (e.g. SQLiteExpert) provide this feature, but I
haven't found a single web based tool that does, and for some websites I
can't afford to copy a DB file to my dev machine, make changes, and copy it
back to production server because I may lose some transactions while I do
that. So, I have to take down the website, which may lead to loss of
business.

So, I am asking developers of SQLite to make it easy for tool developers to
offer the ability to rename attributes.

Roman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users