root / pykota / trunk / initscripts / postgresql / upgrade-from-before-1.03.py @ 3501

Revision 3481, 6.6 kB (checked in by jerome, 16 years ago)

Changed copyright years.
Copyright years are now dynamic when displayed by a command line tool.

  • Property svn:eol-style set to native
  • Property svn:executable set to *
  • Property svn:keywords set to Author Date Id Revision
Line 
1#! /usr/bin/env python
2#
3# PyKota - Print Quotas for CUPS
4#
5# (c) 2003-2009 Jerome Alet <alet@librelogiciel.com>
6# This program is free software: you can redistribute it and/or modify
7# it under the terms of the GNU General Public License as published by
8# the Free Software Foundation, either version 3 of the License, or
9# (at your option) any later version.
10#
11# This program is distributed in the hope that it will be useful,
12# but WITHOUT ANY WARRANTY; without even the implied warranty of
13# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
14# GNU General Public License for more details.
15#
16# You should have received a copy of the GNU General Public License
17# along with this program.  If not, see <http://www.gnu.org/licenses/>.
18#
19# $Id$
20#
21# THIS IS AN UPGRADE SCRIPT FOR OLD PYKOTA DATABASES TO THE 1.03 VERSION
22#
23
24import sys
25import os
26try :
27    import pg
28except ImportError :
29    sys.stderr.write("The PyGreSQL Python module doesn't seem to be available. ABORTED.\n")
30    sys.exit(-1)
31
32def dump_old_database() :
33    """Dumps the existing PyKota database to a file, to avoir loosing data.
34
35       Returns 1 if dump is successfull, 0 if it isn't.
36    """
37    pipeinput = os.popen("pg_dump -C -D -N -U postgres -f pykota-dump.sql pykota")
38    dummy = pipeinput.read()
39    return (pipeinput.close() is None) or 0
40
41def create_new_database() :
42    """Creates the empty database."""
43    pipeinput = os.popen("psql -U postgres template1 <pykota-postgresql.sql")
44    dummy = pipeinput.read()
45    return (pipeinput.close() is None) or 0
46
47def restore_original_database() :
48    """Creates the empty database."""
49    pipeinput = os.popen("psql -U postgres template1 <pykota-dump.sql")
50    dummy = pipeinput.read()
51    return (pipeinput.close() is None) or 0
52
53def open_database(dbname="pykota") :
54    """Returns the database object or None if we can't connect to it."""
55    try :
56        pykotadb = pg.connect(host="localhost", port=5432, dbname=dbname, user="postgres")
57    except pg.error, msg :
58        sys.stderr.write("%s\n" % msg)
59        sys.stderr.write("Unable to connect to the local PostgreSQL server.\nPlease modify the open_database() method in %s\nto connect to the correct PostgreSQL server\nand relaunch the script.\n" % sys.argv[0])
60        return
61    else :
62        return pykotadb
63
64
65def doQuote(field) :
66    """Quotes a field for use as a string in SQL queries."""
67    if type(field) == type(0) : # TODO : do something safer
68        typ = "decimal"
69    else :
70        typ = "text"
71    return pg._quote(field, typ)
72
73def main() :
74    """Does the work."""
75
76    # First we make a dump of the old database
77    print "Dumping old database for safety...",
78    if not dump_old_database() :
79        sys.stderr.write("Error while dumping old database. ABORTED.\n")
80        return -1
81    print "Done."
82
83    # Second we try to connect to it
84    print "Extracting datas from old database...",
85    db = open_database()
86    if db is None :
87        sys.stderr.write("Impossible to connect to old PyKota database. ABORTED.\nAre you sure you are upgrading an existing installation ?\n")
88        return -1
89
90    # Third we extract datas
91    oldprinters = db.query("SELECT * FROM printers ORDER BY id;")
92    oldusers = db.query("SELECT * FROM users ORDER BY id;")
93    oldquotas = db.query("SELECT * FROM userpquota ORDER BY printerid, userid;")
94
95    # Fourth close the database
96    db.close()
97    print "Done."
98
99    # Fifth we delete the old database !
100    answer = raw_input("The old database will be deleted for the upgrade to take place.\nAre you sure you want to continue (y/N) ? ")
101    if answer[0:1].upper() != 'Y' :
102        sys.stderr.write("User wants to stop now ! ABORTED.\n")
103        return -1
104    db = open_database("template1")
105    if db is None :
106        sys.stderr.write("Impossible to connect to database template1. ABORTED.\nPlease report to your Database administrator.\n")
107        return -1
108    try :
109        db.query("DROP DATABASE pykota;")
110    except pg.error, msg :
111        sys.stderr.write("Impossible to delete old database. ABORTED.\n%s\n" % msg)
112        db.close()
113        return -1
114    else :
115        db.close()
116
117    # Sixth we create the new database
118    print "Creating the new database...",
119    if not create_new_database() :
120        sys.stderr.write("impossible to create new database ! ABORTED.\n")
121        return -1
122    print "Done."
123
124    # Seventh we restore old data
125    print "Restoring old datas..."
126    db = open_database()
127    if db is None :
128        sys.stderr.write("Impossible to connect to new PyKota database. ABORTED.\nOld database will be restored if possible.")
129        print "An error occured, I'll try to restore the original database..."
130        if not restore_original_database() :
131            sys.stderr.write("Shit ! A double-error occured !!!\nPlease report problem to your database administrator.\n")
132            sys.stderr.write("And file a bug report to alet@librelogiciel.com\n")
133        else :
134            print "Done."
135        return -1
136    db.query("BEGIN;")
137    try :
138        newprinters = {}
139        for oldprinter in oldprinters.dictresult() :
140            db.query("INSERT INTO printers (printername) VALUES (%s);" % doQuote(oldprinter["printername"]))
141            newid = db.query("SELECT id FROM printers WHERE printername='%s';" % oldprinter["printername"]).dictresult()[0]["id"]
142            newprinters[oldprinter["id"]] = newid
143        newusers = {}
144        for olduser in oldusers.dictresult() :
145            db.query("INSERT INTO users (username) VALUES (%s);" % doQuote(olduser["username"]))
146            newid = db.query("SELECT id FROM users WHERE username='%s';" % olduser["username"]).dictresult()[0]["id"]
147            newusers[olduser["id"]] = newid
148        for oldquota in oldquotas.dictresult() :
149            db.query("INSERT INTO userpquota (userid, printerid, pagecounter, lifepagecounter, softlimit, hardlimit, datelimit) VALUES (%s, %s, %s, %s, %s, %s, %s);" %
150                                              (doQuote(newusers[oldquota["userid"]]) , doQuote(newprinters[oldquota["printerid"]]), doQuote(oldquota["pagecounter"]), doQuote(oldquota["lifepagecounter"]), doQuote(oldquota["softlimit"]), doQuote(oldquota["hardlimit"]), doQuote(oldquota["datelimit"])))
151    except pg.error, msg :
152        sys.stderr.write("ERROR : %s\nABORTED.\n" % msg)
153        db.query("ROLLBACK;")
154        db.close()
155        return -1
156    except :
157        sys.stderr.write("Unknown error ! ABORTED.\n")
158        db.query("ROLLBACK;")
159        db.close()
160        return -1
161    else :
162        db.query("COMMIT;")
163        db.close()
164    print "Done."
165    print "NB : Last job on each printer was lost. This is normal !"
166    return 0
167
168if __name__ == "__main__" :
169    sys.exit(main())
Note: See TracBrowser for help on using the browser.